Linux(CentOs7)Mysql(5.6+)的安装的坑

Linux(CentOs7)Mysql(5.6+)的安装的坑

在整canal的时候折腾 了半天，最后还是5.5的mysql做了升级，摊牌了。。。

• 卸载之前的mysql

1.查看mysql的rpm包
rpm -qa | grep -i mysql
2.停止mysql服务
service mysql stop
3.卸载rpm
rpm -ev MySQL-devel-5.6.23-1.linux_glibc2.5
4. 删除文件
find / -name mysql
5. 安装 devl client server
rpm -ivh xxx
问题
依赖检测失败perl(Data::Dumper) 被 MySQL-server-5.6.42-1.el6.x86_64 需要
yum -y install autoconf

• 安装完server后

A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.

You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.

Also, the account for the anonymous user has been removed.

In addition, you can run:

  /usr/bin/mysql_secure_installation

which will also give you the option of removing the test database.
This is strongly recommended for production servers.

See the manual for more instructions.

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

New default config file was created as /usr/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings

WARNING: Default config file /etc/my.cnf exists on the system
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or use the
--defaults-file argument to mysqld_safe when starting the server

• 得到默认密码

cat /root/.mysql_secret
修改密码
set password=password('root');
密码过期问题
1. 修改user表的password_expired字段设置为N
2. 修改密码
set password for 'root'@'xxx' = password('xxx');

• 修改字符集

#在[client]段增加下面代码 
default-character-set=utf8
#在[mysql]段增加下面的代码 
character-set-server=utf8 
#在[mysqld]段增加下面的代码
character-set-server=utf8
init_connect='SET NAMES utf8' （设定连接mysql数据库时使用utf8编码，以让mysql数据库为utf8运行,也可以不设置）

• 解释

character_set_client：客户端请求数据的字符集。

character_set_connection：从客户端接收到数据，然后传输的字符集。

character_set_database：默认数据库的字符集，无论默认数据库如何改变，都是这个字符集；如果没有默认数据库，使character_set_server指定的字符集，此参数无需设置。

character_set_filesystem：把操作系统上文件名转化成此字符集，即把character_set_client转换character_set_filesystem，默认binary即可。

character_set_results：结果集的字符集。

character_set_server：数据库服务器的默认字符集。

character_set_system：这个值总是utf8，不需要设置，存储系统元数据的字符集。

如果一台服务器上有两个数据库，并且编码格式不一致这样的话就，按照上面在配置文件中设置这character_set_client，character_set_connection，character_set_results三个值没有发生变化。

一个数据库端口是3306，配置文件路径：/etc/my.cnf 编码格式：gbk

一个数据库端口是3307，配置文件路径：/usr/local/mysql3307/my.cnf 编码格式utf8

登录mysql -u root -p 此时默认会加载/etc/my.cnf文件，编码格式会按照这个编码格式进行设置。如果你登录的是端口为3307的数据库查询编码这character_set_client，character_set_connection，character_set_results值会显示gbk，那么如何指定编码，需要在登录进去后执行：set names 'utf8'