mysql常用小知识

以下基于Ubuntu16.04:

卸载:

sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

创建用户(发现已经不建议grant顺带创建用户了):

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
mysql> FLUSH PRIVILEGES;
mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

数据库编码环境:

/etc/mysql/conf.d/mysql.cnf

[mysql]
default-character-set=utf8
[client]
default-character-set=utf8

/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
init-connect='SET NAMES utf8'
character-set-server = utf8


mysql> SHOW VARIABLES LIKE 'character%';

遇到一个很神奇的问题就是

ERROR 1045 (28000): 'Access denied for user 'root'@'localhost' (using password: YES)'

出现这个问题的原因是把旧数据库一股脑打包导入,日志的描述是:

2018-03-16T06:07:52.910261Z 0 [Warning] User entry 'root'@'localhost' has an empty plugin value. The user will be ignored and no one can login with this user anymore.
2018-03-16T06:07:52.910298Z 0 [Warning] Some of the user accounts with SUPER privileges were disabled because of empty mysql.user.plugin value. If you are upgrading from MySQL 5.6 to MySQL 5.7 it means we were not able to substitute for empty plugin column. Probably because of pre 4.1 password hash. If your account is disabled you will need to:
2018-03-16T06:07:52.910303Z 0 [Warning] 1. Stop the server and restart it with --skip-grant-tables.
2018-03-16T06:07:52.910306Z 0 [Warning] 2. Run mysql_upgrade.
2018-03-16T06:07:52.910309Z 0 [Warning] 3. Restart the server with the parameters you normally use.
2018-03-16T06:07:52.910321Z 0 [Warning] For complete instructions on how to upgrade MySQL to a new version please see the 'Upgrading MySQL' section from the MySQL manual

试过很多办法,包括日志中提到的mysql_upgrade,目前试过有效的办法是关闭mysql后,在上文中的mysqld.cnf中mysqld下方加入skip-grant-tables,启动mysql,

mysql -u root -p
mysql> flush privileges;
mysql> update user set plugin='mysql_native_password' where User='root';

然后移除或者注释掉添加的skip部分,重启mysql

MySQL字符集LATIN1转UTF8

导出表结构

mysqldump -uroot -p --default-character-set=utf8 -d databasename > db.sql
修改db.sql内的字符集设置(notepad++编辑)
ENGINE=MyISAM DEFAULT CHARSET=latin1;
修改为
ENGINE=MyISAM DEFAULT CHARSET=utf8;
导出数据
mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 databasename >data.sql
修改data.sql
set names latin1;
改为
set names utf8;//使得客户端和链接使用utf8格式,使得数据以utf8的形式存储

创建数据库

create database databasename default charset utf8;

导入表结构

mysql -uroot -p databasename < db.sql

导入表数据

mysql -uroot -p databasename < data.sql

备注:原理流程
1、导出表结构,把latin1结构替换成utf8
2、用原有的latin1导出数据,并且将set names替换成utf8格式
3、创建数据库,默认字符集改成utf8
4、导入表结构、数据

php与mysql编码

mysql5.6编码修改默认utf8的方法:

一般来说我们都偏向用utf8,但是mysql默认有些设置是latin1,这个通过“show variables like ‘character%’;”可以查看到。

修改/etc/mysql/my.cnf,找到[client] 添加:

default-character-set=utf8

找到[mysqld] 添加:

default-character-set=utf8 //5.5以后版本为character_set_server=utf8,千万注意,否则无法启动

init_connect=’SET NAMES utf8′

然后重启mysql服务。

php输出编码

php输出utf-8的话,首先要保证所读取的数据库是utf8的,然后才能保证页面输出能输出utf8.可以修改nginx或者apache的配置文件,添加charset utf8这样的字段,也可以在有任何输出之前,加入header(“Content-type:text/html;charset=utf-8”);

mysql数据库导入与导出

既不借助myphpadmin,也不借助mysqlworkbench的情况下,单凭mysql命令行导入导出数据库。

MySQL数据库导入: mysql -u 用户名 -p 数据库名 < 文件名

注意:导入的数据库名需要已经存在。如果数据库用户名有密码密码,执行后会提示输入密码。如果数据库用户名没有密码,就将命令中的“-p”参数删除。

MySQL数据库导出:mysqldump -u 用户名 -p 数据库名 > 文件名