专职DBA-MySQL常用的两种升级方法 周万春 MySQL5.5.54升级到MySQL5.6.35 #---------------------------------------------------------------------------------------- 第1种升级方案：in-place原地升级 1.停止MySQL 5.5.54 db01 [~] 2020-06-30 23:07:03 root@pts/0 # mysql -udba -p mysql> show variables like '%sql_mode%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_mode | | +---------------+-------+ 1 row in set (0.00 sec) mysql> set global innodb_fast_shutdown=0; Query OK, 0 rows affected (0.00 sec) db01 [~] 2020-06-30 23:08:35 root@pts/2 # /etc/init.d/mysqld stop Shutting down MySQL.... [ OK ] db01 [~] 2020-06-30 23:09:03 root@pts/2 # ps -ef | grep mysqld | grep -v grep db01 [~] 2020-06-30 23:09:09 root@pts/2 # netstat -lnp | grep mysqld 2.在my.cnf中添加skip_grant_tables参数 db01 [~] 2020-06-30 23:09:45 root@pts/2 # cat /etc/my.cnf [client] socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径 [mysqld] user=mysql basedir = /usr/local/mysql socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径 pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid # pid文件所在路径 datadir=/home/mysql/data/mysqldata1/mydata # 数据文件路径 tmpdir=/home/mysql/data/mysqldata1/tmpdir # 存放临时文件的路径 log-error=/home/mysql/data/mysqldata1/log/error.log slow_query_log slow_query_log_file=/home/mysql/data/mysqldata1/slowlog/slow-query.log log-bin=/home/mysql/data/mysqldata1/binlog/mysql-bin relay-log=/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin innodb_data_home_dir = /home/mysql/data/mysqldata1/innodb_ts innodb_log_group_home_dir = /home/mysql/data/mysqldata1/innodb_log #innodb_undo_directory = /home/mysql/data/mysqldata1/undo/ skip_grant_tables 3.替换basedir db01 [~] 2020-06-30 23:09:49 root@pts/2 # cd /disk/ db01 [/disk] 2020-06-30 23:11:12 root@pts/2 # tar -xf mysql-5.6.35-linux-glibc2.5-x86_64.tar.gz -C /home/mysql/program/ db01 [/disk] 2020-06-30 23:11:45 root@pts/2 # unlink /usr/local/mysql db01 [/disk] 2020-06-30 23:11:57 root@pts/2 # ln -s /home/mysql/program/mysql-5.6.35-linux-glibc2.5-x86_64/ /usr/local/mysql db01 [/disk] 2020-06-30 23:12:11 root@pts/2 # chown -R mysql:mysql /home/mysql/program/mysql-5.6.35-linux-glibc2.5-x86_64/ 4.备份数据 db01 [/disk] 2020-06-30 23:13:12 root@pts/2 # cd /home/mysql/ db01 [/home/mysql] 2020-06-30 23:14:32 root@pts/2 # cp -a data data_bak 5.启动并升级MySQL db01 [~] 2020-06-30 23:16:04 root@pts/0 # cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld db01 [~] 2020-06-30 23:16:19 root@pts/0 # chmod +x /etc/init.d/mysqld db01 [~] 2020-06-30 23:16:29 root@pts/0 # /etc/init.d/mysqld start Starting MySQL.. [ OK ] db01 [~] 2020-06-30 23:17:22 root@pts/0 # mysql mysql> select user(),current_user(); +--------+----------------+ | user() | current_user() | +--------+----------------+ | root@ | @ | +--------+----------------+ 1 row in set (0.00 sec) 使用mysql_upgrade命令升级数据字典库。注意：使用mysql_upgrade命令时需要用管理员账号，且带上用户名和密码，否则会报出拒绝访问的错误。 注意：过程中每一步都要输出OK，且最后输出一个总的OK，看到这些OK，就表示所有的数据字典表升级成功了。 db01 [~] 2020-06-30 23:18:13 root@pts/0 # mysql_upgrade -uroot -p Enter password: Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck' with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock' Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck' with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock' Warning: Using a password on the command line interface can be insecure. mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Running 'mysql_fix_privilege_tables'... Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck' with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock' Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck' with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock' Warning: Using a password on the command line interface can be insecure. app01.t1 OK OK 6.重启MySQL并访问数据，测试升级之后能否正常访问 db01 [~] 2020-06-30 23:19:54 root@pts/0 # cat /etc/my.cnf [client] socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径 [mysqld] user=mysql basedir = /usr/local/mysql socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径 pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid # pid文件所在路径 datadir=/home/mysql/data/mysqldata1/mydata # 数据文件路径 tmpdir=/home/mysql/data/mysqldata1/tmpdir # 存放临时文件的路径 log-error=/home/mysql/data/mysqldata1/log/error.log slow_query_log slow_query_log_file=/home/mysql/data/mysqldata1/slowlog/slow-query.log log-bin=/home/mysql/data/mysqldata1/binlog/mysql-bin relay-log=/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin innodb_data_home_dir = /home/mysql/data/mysqldata1/innodb_ts innodb_log_group_home_dir = /home/mysql/data/mysqldata1/innodb_log #innodb_undo_directory = /home/mysql/data/mysqldata1/undo/ #skip_grant_tables db01 [~] 2020-06-30 23:19:56 root@pts/0 # /etc/init.d/mysqld restart Shutting down MySQL.. [ OK ] Starting MySQL. [ OK ] db01 [~] 2020-06-30 23:21:19 root@pts/0 # mysql -udev -p app01 mysql> select user(),current_user(); +---------------+----------------+ | user() | current_user() | +---------------+----------------+ | dev@localhost | dev@% | +---------------+----------------+ 1 row in set (0.00 sec) mysql> select version(); +------------+ | version() | +------------+ | 5.6.35-log | +------------+ 1 row in set (0.00 sec) mysql> show grants; +--------------------------------------------------------------------------------------------------------+ | Grants for dev@% | +--------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'dev'@'%' IDENTIFIED BY PASSWORD <secret> | | GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `app01`.* TO 'dev'@'%' | +--------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> select * from t1; +----+--------------------------------------+------+------+---------------------+ | id | c1 | c2 | c3 | intime | +----+--------------------------------------+------+------+---------------------+ | 1 | bf7042b9-bae2-11ea-b650-00163e1693be | db01 | 3306 | 2020-06-30 23:02:42 | +----+--------------------------------------+------+------+---------------------+ 1 row in set (0.00 sec) mysql> insert into t1(c1,c2,c3) values(uuid(),@@hostname,@@port); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> select * from t1; +----+--------------------------------------+------+------+---------------------+ | id | c1 | c2 | c3 | intime | +----+--------------------------------------+------+------+---------------------+ | 1 | bf7042b9-bae2-11ea-b650-00163e1693be | db01 | 3306 | 2020-06-30 23:02:42 | | 2 | 83d8dcbc-bae5-11ea-8ebf-00163e1693be | db01 | 3306 | 2020-06-30 23:22:31 | +----+--------------------------------------+------+------+---------------------+ 2 rows in set (0.00 sec) 如果需要修改sql_mode值，则修改全局，并把sql_mode加到my.cnf中 db01 [~] 2020-06-30 23:23:19 root@pts/0 # mysql -udba -p mysql> show variables like '%sql_mode%'; +---------------+------------------------+ | Variable_name | Value | +---------------+------------------------+ | sql_mode | NO_ENGINE_SUBSTITUTION | +---------------+------------------------+ 1 row in set (0.00 sec) mysql> set global sql_mode=''; Query OK, 0 rows affected (0.00 sec) #---------------------------------------------------------------------------------------- 第2张升级方案：mysqldump逻辑导出导入升级 环境准备到MySQL5.5 db01 [~] 2020-06-30 23:28:22 root@pts/0 # /etc/init.d/mysqld stop Shutting down MySQL.. [ OK ] db01 [~] 2020-06-30 23:28:34 root@pts/0 # unlink /usr/local/mysql db01 [~] 2020-06-30 23:29:10 root@pts/0 # cd /home/mysql/ db01 [/home/mysql] 2020-06-30 23:29:34 root@pts/0 # mv data data_5.6 db01 [/home/mysql] 2020-06-30 23:29:51 root@pts/0 # mv data_bak data db01 [~] 2020-06-30 23:30:33 root@pts/0 # ln -s /home/mysql/program/mysql-5.5.54-linux2.6-x86_64/ /usr/local/mysql db01 [~] 2020-06-30 23:30:46 root@pts/0 # rm -rf /etc/init.d/mysqld db01 [~] 2020-06-30 23:30:58 root@pts/0 # cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld db01 [~] 2020-06-30 23:31:31 root@pts/0 # /etc/init.d/mysqld start Starting MySQL.. [ OK ] 以上MySQL5.5.54数据库环境已经就绪 1.使用mysqldump备份整个实例 db01 [~] 2020-06-30 23:31:53 root@pts/0 # mysql -udba -p mysql> show variables like '%sql_mode%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_mode | | +---------------+-------+ 1 row in set (0.00 sec) mysql> flush table with read lock; Query OK, 0 rows affected (0.00 sec) mysql> set global read_only=ON; Query OK, 0 rows affected (0.00 sec) mysql> q Bye db01 [~] 2020-06-30 23:50:13 root@pts/4 # mysqldump -udba -p --add-drop-table --routines --events --all-databases --force > /tmp/data-for-upgrade.sql Enter password: 2.安装MySQL 5.6.35 db01 [~] 2020-06-30 23:51:10 root@pts/4 # /etc/init.d/mysqld stop Shutting down MySQL.. [ OK ] db01 [~] 2020-06-30 23:51:33 root@pts/4 # ps -ef | grep mysqld | grep -v grep db01 [~] 2020-06-30 23:51:37 root@pts/4 # netstat -lnp | grep mysqld db01 [~] 2020-06-30 23:51:43 root@pts/4 # unlink /usr/local/mysql db01 [~] 2020-06-30 23:56:21 root@pts/0 # ln -s /home/mysql/program/mysql-5.6.35-linux-glibc2.5-x86_64/ /usr/local/mysql db01 [~] 2020-06-30 23:52:37 root@pts/4 # cd /home/mysql/ db01 [/home/mysql] 2020-06-30 23:52:54 root@pts/4 # cp -a data data_bak db01 [/home/mysql] 2020-06-30 23:53:37 root@pts/4 # cd data/mysqldata1/ db01 [/home/mysql/data/mysqldata1] 2020-06-30 23:55:03 root@pts/4 # rm -rf ./{binlog,innodb_log,innodb_ts,log,mydata,slowlog,sock,tmpdir,undo}/* db01 [/home/mysql/data/mysqldata1] 2020-06-30 23:55:08 root@pts/4 # tree ./ ./ |-- binlog |-- innodb_log |-- innodb_ts |-- log |-- mydata |-- relaylog |-- slowlog |-- sock |-- tmpdir `-- undo 10 directories, 0 files db01 [/home/mysql/data/mysqldata1] 2020-06-30 23:56:44 root@pts/0 # cd /usr/local/mysql/ db01 [/usr/local/mysql] 2020-06-30 23:56:47 root@pts/0 # ./scripts/mysql_install_db --defaults-file=/home/mysql/conf/my.cnf --user=mysql 3.导入MySQL 5.5.54的备份数据 db01 [~] 2020-06-30 23:58:09 root@pts/0 # rm -rf /etc/init.d/mysqld db01 [~] 2020-06-30 23:58:14 root@pts/0 # cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld db01 [~] 2020-06-30 23:58:23 root@pts/0 # chmod +x /etc/init.d/mysqld db01 [~] 2020-07-01 00:12:34 root@pts/0 # cat /etc/my.cnf [client] socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径 [mysqld] user=mysql basedir = /usr/local/mysql socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径 pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid # pid文件所在路径 datadir=/home/mysql/data/mysqldata1/mydata # 数据文件路径 tmpdir=/home/mysql/data/mysqldata1/tmpdir # 存放临时文件的路径 log-error=/home/mysql/data/mysqldata1/log/error.log slow_query_log slow_query_log_file=/home/mysql/data/mysqldata1/slowlog/slow-query.log log-bin=/home/mysql/data/mysqldata1/binlog/mysql-bin relay-log=/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin innodb_data_home_dir = /home/mysql/data/mysqldata1/innodb_ts innodb_log_group_home_dir = /home/mysql/data/mysqldata1/innodb_log #innodb_undo_directory = /home/mysql/data/mysqldata1/undo/ skip_grant_tables db01 [~] 2020-06-30 23:58:33 root@pts/0 # /etc/init.d/mysqld start Starting MySQL. [ OK ] db01 [~] 2020-07-01 00:00:24 root@pts/0 # mysql -f < /tmp/data-for-upgrade.sql db01 [~] 2020-07-01 00:00:54 root@pts/0 # echo $? 0 4.执行mysql_upgrade升级数据字典库 db01 [~] 2020-07-01 00:13:31 root@pts/0 # mysql_upgrade -uroot -p Enter password: Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck' with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock' Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck' with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock' Warning: Using a password on the command line interface can be insecure. mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.innodb_index_stats OK mysql.innodb_table_stats OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.proxies_priv OK mysql.servers OK mysql.slave_master_info OK mysql.slave_relay_log_info OK mysql.slave_worker_info OK mysql.slow_log OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK Running 'mysql_fix_privilege_tables'... Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck' with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock' Warning: Using a password on the command line interface can be insecure. Running 'mysqlcheck' with connection arguments: '--socket=/home/mysql/data/mysqldata1/sock/mysql.sock' Warning: Using a password on the command line interface can be insecure. app01.t1 OK OK 5.重启MySQL并访问数据，测试升级之后能否正常访问 去掉配置文件中的skip_grant_tables参数并重启MySQL db01 [~] 2020-07-01 00:14:29 root@pts/0 # cat /etc/my.cnf [client] socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径 [mysqld] user=mysql basedir = /usr/local/mysql socket=/home/mysql/data/mysqldata1/sock/mysql.sock # sock文件所在路径 pid-file=/home/mysql/data/mysqldata1/sock/mysql.pid # pid文件所在路径 datadir=/home/mysql/data/mysqldata1/mydata # 数据文件路径 tmpdir=/home/mysql/data/mysqldata1/tmpdir # 存放临时文件的路径 log-error=/home/mysql/data/mysqldata1/log/error.log slow_query_log slow_query_log_file=/home/mysql/data/mysqldata1/slowlog/slow-query.log log-bin=/home/mysql/data/mysqldata1/binlog/mysql-bin relay-log=/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin innodb_data_home_dir = /home/mysql/data/mysqldata1/innodb_ts innodb_log_group_home_dir = /home/mysql/data/mysqldata1/innodb_log #innodb_undo_directory = /home/mysql/data/mysqldata1/undo/ #skip_grant_tables db01 [~] 2020-07-01 00:14:32 root@pts/0 # /etc/init.d/mysqld restart Shutting down MySQL.. [ OK ] Starting MySQL. [ OK ] db01 [~] 2020-07-01 00:15:21 root@pts/0 # mysql -udev -p app01 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | app01 | +--------------------+ 2 rows in set (0.00 sec) mysql> select * from t1; +----+--------------------------------------+------+------+---------------------+ | id | c1 | c2 | c3 | intime | +----+--------------------------------------+------+------+---------------------+ | 1 | bf7042b9-bae2-11ea-b650-00163e1693be | db01 | 3306 | 2020-06-30 23:02:42 | +----+--------------------------------------+------+------+---------------------+ 1 row in set (0.00 sec) mysql> insert into t1(c1,c2,c3) values(uuid(),@@hostname,@@port); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> select * from t1; +----+--------------------------------------+------+------+---------------------+ | id | c1 | c2 | c3 | intime | +----+--------------------------------------+------+------+---------------------+ | 1 | bf7042b9-bae2-11ea-b650-00163e1693be | db01 | 3306 | 2020-06-30 23:02:42 | | 2 | 04e0160f-baed-11ea-bff3-00163e1693be | db01 | 3306 | 2020-07-01 00:16:14 | +----+--------------------------------------+------+------+---------------------+ 2 rows in set (0.00 sec) db01 [~] 2020-07-01 00:17:03 root@pts/0 # mysql -udba -p mysql> show variables like '%sql_mode%'; +---------------+--------------------------------------------+ | Variable_name | Value | +---------------+--------------------------------------------+ | sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | +---------------+--------------------------------------------+ 1 row in set (0.00 sec) 如果需要修改sql_mode值，则修改全局，并把sql_mode加到my.cnf中 mysql> set global sql_mode='NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected (0.00 sec) 升级注意事项 在主库中创建一个带有主键的InnoDB表（低于MySQL 5.7版本的库），针对主键字段设置为null(虽然这个例子不是很恰当，但这里仅限于说明从MySQL 5.6升级到MySQL 5.7需要谨慎，在同一个复制架构中不建议存在多个版本) mysql> show create table test; ...... 1 row in set (0.00 sec) mysql> select version(); ...... 1 row in set (0.00 sec) mysql> alter table test modify column id int(10) unsigned NULL; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test; ...... 1 row in set (0.00 sec) ## 解析binlog查看 [root@localhost data]# mysqlbinlog -vv mysql-bin.000203 ...... # 在主库中创建一个带有主键的InnoDB表（高于或等于MySQL 5.7版本的库），针对主键字段设置为null mysql> show create table test; ...... 1 row in set (0.00 sec) mysql> select version(); ...... 1 row in set (0.00 sec) mysql> alter table test modify column id int(10) unsigned NULL; ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead MySQL5.7版本不允许对主键设置null属性操作，MySQL5.6版本是允许对主键设置null属性操作的， 但会在存储引擎内部自动忽略这个动作。 虽然忽略了这个动作，但是这条语句却执行成功了，已经记录到binlog中。 如果从库是MySQL5.7，就会在SQL线程重放到这条SQL语句时报错， ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead.