第2章 MySQL常用的两种升级方法

2.3.1

• 1．停止MySQL 5.5.54

mysql> show variables like '%sql_mode%';
......
1 row in set (0.00 sec)

[root@localhost mysql]# mysql -ugangshen -p
......

mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)

[root@ localhost mysql]# service mysqld stop
Shutting down MySQL..                                      [  OK  ]

# 确认MySQL已经停止
[root@ localhost mysql]# ps aux |grep mysqld_safe |grep -v grep
[root@ localhost mysql]# netstat -ntupl |grep mysqld
tcp      0     0 :::9104              :::*               LISTEN      1968/mysqld_exporte 

• 2．在my.cnf中添加skip_grant_tables参数

[root@localhost mysql]# 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
skip_grant_tables

• 3．替换basedir

[root@localhost mysql]# cd /usr/local/
[root@localhost local]# ll
......
[root@ localhost local]# unlink mysql
[root@ localhost local]# ll
...... 
[root@localhost local]# ln -s /home/mysql/ program/mysql- 5.6.35-linux-glibc2.5-x86_64/\
/usr/local/mysql
[root@localhost local]# ll /usr/local/
......

• 4．备份数据

[root@localhost mysql]# cd /home/mysql/
[root@localhost mysql]# cp -ar data/ data.bak
[root@localhost mysql]# ll
......

• 5．启动并升级MySQL

[root@localhost local]# service mysqld start
Starting MySQL...                                          [  OK  ]

# 直接使用mysql命令测试是否可以免密码登录
[root@localhost local]# mysql
......
mysql> Ctrl-C -- exit!
Aborted

# 使用mysql_upgrade命令升级数据字典库。注意：使用mysql_upgrade命令时需要用管理员账号，且带上用户名和密码，否则会报出拒绝访问的错误
[root@localhost local]# mysql_upgrade -uroot -p
......
# 注意：过程中每一步都要输出OK，且最后输出一个总的OK，看到这些OK，就表示所有的数据字典表升级成功了

• 6．重启MySQL并访问数据，测试升级之后能否正常访问

[root@localhost local]# 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
# skip_grant_options

[root@localhost local]# service mysqld restart
Shutting down MySQL..                                    	[  OK  ]
......
[root@localhost local]# mysql -uprogram -p
......
mysql> select user();
+-------------------+
| user()             |
+-------------------+
| program@localhost |
+-------------------+
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;
......
2 rows in set (0.00 sec)

# 访问用户数据
mysql> show databases;
......
2 rows in set (0.01 sec)

mysql> use shengang_db
......
mysql> show tables;
......
1 row in set (0.00 sec)

mysql> select * from shengang_table;
......
1 row in set (0.00 sec)

mysql> insert into shengang_table(shengang_test,datetime_current) values('shengang', now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from shengang_table;
+----+---------------+---------------------+
......
2 rows in set (0.00 sec)

mysql> show variables like '%sql_mode%'\G
......
mysql> set global sql_mode=''; # 如果需要修改sql_mode值，则修改全局，并把sql_mode加到my.cnf中
Query OK, 0 rows affected (0.00 sec)

2.3.2　使用mysqldump逻辑备份数据

• 2．使用mysqldump备份整个实例

mysql> show variables like '%sql_mode%'\G
......
1 row in set (0.00 sec)

[root@localhost mysql]# mysql -ugangshen -p
......

mysql> flush table with read lock;
Query OK, 0 rows affected (0.01 sec)

mysql> set global read_only=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> Ctrl-C -- exit!

Aborted[root@ localhost local]# mysqldump -u root -p --add-drop-table --routines –events\ --all-databases --force > /home/mysql/data/data-for- upgrade.sql
Enter password: 
[root@localhost local]# vim /home/mysql/data/data-for-upgrade.sql 
[root@localhost local]# vim /home/mysql/data/data-for-upgrade.sql 
[root@localhost local]# service mysqld stop
Shutting down MySQL..                                      [  OK  ]
[root@localhost local]# ll /home/mysql/data/data-for-upgrade.sql 
-rw-r--r-- 1 root root 556738 Feb 13 19:31 /home/mysql/data/ data-for-upgrade.sql

• 3．安装MySQL 5.6.35

[root@localhost local]# service mysqld stop
Shutting down MySQL..                                      [  OK  ]
# 查看MySQL是否停止成功
[root@localhost local]# ps aux |grep mysqld_safe
root     28775  0.0  0.0 103252   844 pts/0    S+   18:47   0:00 grep mysqld_safe
[root@localhost local]# netstat -ntupl |grep mysqld
tcp        0      0 :::9104            :::*              LISTEN      1968/mysqld_exporte 

# 解压缩MySQL 5.6.35二进制安装文件
[root@localhost mysql]# cd 
[root@localhost ~]# ll
......
[root@localhost ~]# tar xvf mysql-5.6.35-linux-glibc2.5- x86_64.tar.gz -C /home/mysql/\
program/

# 替换basedir
[root@localhost local]# cd /usr/local/
[root@localhost local]# ll
......
[root@localhost local]# unlink mysql
[root@localhost local]# ln -s /home/mysql/program/mysql- 5.6.35-linux-glibc2.5-x86_64/\
/usr/local/mysql
[root@localhost local]# ll 
......

# 备份数据目录
[root@localhost local]# cd /home/mysql/
[root@localhost mysql]# ll
......
[root@localhost mysql]# cp -ar data/ data.bak
[root@localhost mysql]# ll
......
[root@localhost mysql]# cd data/mysqldata1/
[root@localhost mysqldata1]# ll
......
# 清理MySQL 5.5.54的数据目录
[root@localhost mysqldata1]# rm -rf {binlog,innodb_log, innodb_ts,log,mydata,slowlog,\
sock,tmpdir,undo}/*
[root@localhost mysqldata1]# tree .
.
......

9 directories, 0 files

[root@localhost mysql]# cd /usr/local/mysql/
[root@localhost mysql]# ./scripts/mysql_install_db--defaults-file= /home/mysql/conf/my.cnf\
--user=mysql
......

[root@localhost mysql]# ll /home/mysql/data/mysqldata1/ {mydata,innodb_log,innodb_ts}
......
[root@localhost mysql]#

• 4．导入MySQL 5.5.54的备份数据

[root@localhost ~]# service mysqld start
Starting MySQL.                                            [  OK  ]

[root@localhost mysql]# mysql --force < /home/mysql/data/ data-for-upgrade.sql 
[root@localhost mysql]# echo $?
0

[root@localhost ~]# mysql -uroot -p --force < /home/mysql/ data/data-for-upgrade.sql
.... 

[root@localhost ~]# which mysql
/usr/bin/mysql
[root@localhost ~]# mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
[root@localhost ~]# rpm -qa |grep mysql
......
[root@localhost ~]# rpm -e mysql-5.1.73- 3.el6_5.x86_64
......
[root@localhost ~]# rpm -e mysql-5.1.73- 3.el6_5.x86_64 --nodeps
[root@localhost ~]# which mysql
/usr/local/mysql/bin/mysql

# 重新加载环境变量
[root@localhost ~]# source /etc/profile

# 到了这里，请重做本章节

• 5．执行mysql_upgrade升级数据字典库

[root@localhost mysql]# mysql_upgrade -uroot -p
......

• 6．重启MySQL并访问数据，测试升级之后能否正常访问

# 去掉配置文件中的skip_grant_tables参数并重启MySQL
[root@localhost mysql]# vim /etc/my.cnf
[root@localhost mysql]# service mysqld restart
Shutting down MySQL..                                     	[  OK  ]
Starting MySQL.                                           	[  OK  ]

# 使用程序账号访问用户数据
[root@localhost mysql]# mysql -uprogram -p
......

mysql> show databases;
......
2 rows in set (0.00 sec)

mysql> use shengang_db
......
mysql> show tables;
......
1 row in set (0.00 sec)

mysql> select * from shengang_table;
......
1 row in set (0.00 sec)

mysql> insert into shengang_table(shengang_test,datetime_current) values('shengang', now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from shengang_table;
......
2 rows in set (0.00 sec)

mysql> 

mysql> show variables like '%sql_mode%'\G
......
1 row in set (0.00 sec)

mysql> set global sql_mode='NO_ENGINE_SUBSTITUTION';  # 如果需要修改sql_mode值，则修改全局，并把sql_mode加到my.cnf中
Query OK, 0 rows affected (0.00 sec)

2.4　升级注意事项

# 在主库中创建一个带有主键的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

---

上一篇：第1章　MySQL初始化安装、简单安全加固 | 下一篇：第3章　MySQL体系结构