/data/mysql/bin/mysql_upgrade_第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