作者：xugang9804

mysql5.6升级到5.7

mysql升级可以用mysql_upgrade更新系统库，5.7版本的系统库和5.6差别巨大，这种方式有诸多风险，没有尝试，这里采取另外一种方式。

5.7版本的安装目录和数据目录，都重新指定，和5.6区分开，5.7安装过程如下：

mkdir /data/mysql57
chmod +w /data/mysql57
chown -R mysql:mysql /data/mysql57

mysql.com下载带boost的源码包，解压

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql5710 -DMYSQL_DATADIR=/data/mysql57 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DMYSQL_UNIX_ADDR==/data/mysql57/mysql.sock -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_BOOST=/usr/local/src/mysql-5.7.16/boost
make
make install

完成安装，接下来初始化5.7系统库，初始化前注意原my.cnf配置文件，很多配置项和5.7不兼容了，先另存，改成5.7默认配置文件

初始化系统库

/usr/local/mysql5710/bin/mysqld –initialize –user=mysql

记住初始化后的给的默认密码，后面首次登录会用到

启动mysql

/usr/local/mysql5710/bin/mysqld_safe &

客户端登录mysql

/usr/local/mysql5710/bin/mysql -h 127.0.0.1 -u root -p

输入密码

提示：You must reset your password using ALTER USER statement before executing this statement.

直接修改密码：

SET PASSWORD = PASSWORD(‘123456′);

进入mysql库，修改几个innodb表引擎，方便后面直接覆盖ibdata1文件

alter table engine_cost engine=’myisam’;
alter table gtid_executed engine=’myisam’;
alter table help_category engine=’myisam’;
alter table help_keyword engine=’myisam’;
alter table help_relation engine=’myisam’;
alter table help_topic engine=’myisam’;
alter table innodb_index_stats engine=’myisam’;
alter table innodb_table_stats engine=’myisam’;
alter table plugin engine=’myisam’;
alter table server_cost engine=’myisam’;
alter table servers engine=’myisam’;
alter table slave_master_info engine=’myisam’;
alter table slave_relay_log_info engine=’myisam’;
alter table slave_worker_info engine=’myisam’;
alter table time_zone engine=’myisam’;
alter table time_zone_leap_second engine=’myisam’;
alter table time_zone_name engine=’myisam’;
alter table time_zone_transition engine=’myisam’;
alter table time_zone_transition_type engine=’myisam’;

停掉mysql

将5.6数据目录的ibdata1和数据库目录转移到5.7数据目录下

注意转移过来文件的写权限

再次启动mysql

将系统表几个改成myisam引擎的表再改回innodb，至此完美升级完mysql，且最快恢复数据，直接拷贝数据文件，比mysqldump导sql再导入快多了

mysql通过mysqldump+binlog备份恢复&mysql5.7.10安装恢复

mysqldump备份脚本范例

#!/bin/sh
DATE1=`date +%Y%m%d%H%M`
DATABASE=glm_ec
BACKUP=/data/backup
/usr/local/mysql/bin/mysqldump -uroot -h 127.0.0.1 -R –single-transaction –master-data=2 库名 |gzip > $BACKUP/$DATABASE`date +%Y%m%d%H%M`.sql.gz

其中有两个参数： –single-transaction –master-data=2，

master-data，将会输出change master命令到导出文件里，值2以备注信息输出到文件里。single-transaction会发起一个读锁，保证当前备份时数据一致性，同时不影响备份时的更新操作，不影响系统的正常运行。库里有多个表，不设置这些参数，在导出时每个表导出的时间点不一样，在binlog里的位置也就不一致，这样在结合binlog文件恢复时，会有很多同步点，这是无法进行操作的。

有了这个唯一的恢复点，结合binlog通过mysqlbinlog命令很容易恢复数据，恢复过程略

分割线

—————————————————————————————–

通过ibdata1以及数据目录（idb以及frm）恢复

前面通过mysqldump+binlog恢复的方法是可行的，但是数据量大的时候，耗时非常长，实际环境数据目录+ibdata1文件超过200G，导sql文件就要十几个小时，速度太慢，因此有另外一种恢复方法，备份数据目录文件和ibdata1文件，通过一个目录和文件来恢复，这种方法优点是快，缺点是备份时需要停机。

恢复过程：

1、停掉要恢复机的mysql，备份ibdata1以及数据目录

2、将ibdata1以及数据目录移到要恢复机，修改移过来文件及目录权限

3、启动恢复机的mysql，如果有异常，还原步骤1备份的文件恢复

分割线

——————————————————————-

mysql5.7.10安装

mysql5.6和5.7的系统库（mysql库）差异非常大，上面第二种ibdata1+数据目录恢复的方案只能用在同版本数据库，本次遇到的情况是需要将mysql5.7备份的文件恢复到mysql5.6版本，经过实操证明这是不可行的，只有先升级mysql版本。

1、下载带有boost版本的community 版 mysql5.7(mysql.com 寻找要下版本的source code版，带boost的一般是最后一个，文件名里有boost)，不带boost版本在安装时实际上还是要在线下载boost，会非常慢

2、CMAKE，要带上boost参数并制定路径，否则在后面make时会报错

3、make & make install

安装过程和其他版本差异不大，略

4、初始系统库，这步要人工操作，/usr/local/mysql/bin/mysqld –initialize –user=mysql

5、按照 恢复2操作，恢复并启动mysql

编译php5.6时报undefined reference to libiconv

config后编辑Makefile，在最后加上 -liconv，如下，再make make install

EXTRA_LIBS = -lcrypt -lz -lcrypt -lrt -lmysqlclient -lmcrypt -lldap -llber -lfreetype -lpng -lz -ljpeg -lcurl -lz -lrt -lm -ldl -lnsl -lrt -lxml2 -lz -lm -lssl -lcrypto -ldl -lz -lcurl -ldl -lgssapi_krb5 -lkrb5 -lk5crypto -lcom_err -lidn -lssl -lcrypto -lz -lxml2 -lz -lm -lssl -lcrypto -ldl -lz -lxml2 -lz -lm -lxml2 -lz -lm -lcrypt -lxml2 -lz -lm -lxml2 -lz -lm -lxml2 -lz -lm -lxml2-lz -lm -lcrypt -liconv

多主一从部分从库重新同步&从库常见异常处理

主机已经运行一段时间，从机重新配置，我们的主从架构是多主一从，从机已经有了其他主机正在同步，所有表都是innodb。

1、停掉主服务器

包括mysql服务，web服务，最主要的mysql服务器一定要关闭

2、  将主服务器ibdata1以及库文件夹整体打包

ibdata1压缩打包，数据库文件压缩打包（tar -zcvf dir_name.tar.gz  dir_name 注：dir_name为被打包的文件目录）

3、  主服务器开启bin-log（my.cnf里增加配置log-bin=mysql-bin），启动，记录binlog的文件初始位置，恢复主服务器环境（需要一个小时）

4、  把打包文件送到从服务器

rsync 例如：rsync ibdata1 10.10.12.xxx:/data 是将ibdata1 传送到10.10.12.xxx的data目录，这时会提示输入10.10.12.xxx的用户名和密码

5、  从服务器同步全部停掉（stop slave），web关掉，mysql服务关闭（service mysqld stop）

6、  备份从服务器ibdata1文件（重命名），当复制过来的数据库表引擎转换成myisam引擎后，可重新恢复此文件

7、  将主服务器打包的数据文件（第2部打包的文件），移到从服务器数据库数据目录(压缩的文件记得解压)；复制过来的ibdata1要有读取和写入的权限

8、  将要同步的库（bala_ec）所有表全部转成myisam引擎，这时复制过来的ibdata1文件可以删除了

转化数据库表引擎的方法参考后面语句

9、  将第4部重命名的ibdata1恢复，这个时候恢复到从库原先的数据状态

10、              再把要同步的库所有表全部转成innodb引擎

11、              设置从服务器的从库配置，启动主从同步—命令2

12、              在从库中启动主从同步 start slave

 

附：

命令1

show master status;

记录master_log_file、master_log_pos

命令2

Change master to master_host=’10.10.12.yy’, master_user=’yyyyy’, master_password=’yyyyy’, master_log_file=’主服同步bin文件名’, master_log_pos=主服同步bin文件的位置 for channel “xxxxx”;

在从库启动主从同步

Start slave

查看从库状态

Show slave status [for channel “”]

改变数据库表引擎：

alter table `acl_level` engine=myisam;

查看数据库中所有的表示例：

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘yyyy’;

查看数据库中表引擎为MyISAM的表示例：

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘yyyy’ AND ENGINE = ‘MyISAM’;

把sql语句执行结果写入到文件

mysql -u root -p -e “use bala_ec;show table status” > /data/1.sql

从库常见异常处理

1、因idbdata1文件里有垃圾表结构，导致myisam无法转innodb，（报表已存在，重启后会在数据目录自动生成idb文件）

先删除表，再重建表

2、同样idbdata1里有垃圾表结构，导致truncate myisam表后表无法创建也无法删除，拷贝一个frm文件过来，然后再删除表，再重建表

3、从库无法启动，报当前主库日志文件当前位置的sql无法执行，在主机使用

mysqlbinlog –start-position=报出问题的位置 –stop-position=往后加几十行 -d 主库名 binlog文件名

看一下指定位置是什么语句，如果不是正常语句位置，可以过去，用change master往后跳到下一条语句的位置

 

mysql5.7多主一从，从机调优

多主一从架构见前面文章，本文略。

同步了三个库到一台从机后，发现有两个库延时特别严重，且与主库延迟时间越拉越长，查看延迟时间，用show slave status;看Seconds_Behind_Master的值。

为了查找原因，准备开启慢日志，发现mysql从5.6开始慢日志配置项就改了，新的配置项有这么几个：

long_query_time=2
slow_query_log = 1
slow_query_log_file=/xx/xx/slow.log

配置好重启后，发现slow.log是生成了，但是等了一段时间后，没有记录到一条慢sql，通过show processlist发现慢sql多的是，就是无记录，暂未解决这个问题。

没办法，只好用show processlist人工检查慢sql，逐条优化。

之前为了初始多台主服的数据，将导过来的从库挨个改成了myisam引擎，为了加快同步速度，又改回innodb，发现在mysql5.7版本下，innodb确实要比myisam快。

经过一段时间观察，优化了几个关键的慢sql，同步延迟终于改善