一、备份

1、mysqldump备份

mysqldump -u root -p --all-databases > /alldatabases_`date +%Y%m%d`.sql

2、数据目录备份

cp -a /usr/local/mysql/data /tmp/usr_local_mysql_data_bak`date +%Y%m%d`

二、关闭MYSQL

1、服务重启测试

/etc/init.d/mysqld restart

2、关闭服务

/etc/init.d/mysqld stop

三、MYSQL升级过程

1、安装mysql-5.7.19

官网下载mysql的二进制版本：

cd /usr/local/

tar zxf mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz

unlink mysql

ln -s mysql-5.7.19-linux-glibc2.12-x86_64 mysql

cd mysql

mkdir mysql-files

chmod 750 mysql-files

chown -R mysql .

chgrp -R mysql .

bin/mysqld --initialize --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql

chown -R root .

chown -R mysql data mysql-files

bin/mysqld_safe --user=mysql &

service mysqld start

2、修改root密码

mysql -uroot -p'oh;uirYW!6<*' //初始化时提供的密码

mysql> alter user 'root'@'linuxyunwei' identified by '123456';

mysql> flush privileges;

mysql> quit

3、测试新密码登陆

/usr/local/mysql/bin/mysql -uroot -p123456

4、列出数据库

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

5、导入之前备份的数据

/usr/local/mysql/bin/mysql -uroot -p123456 < /alldatabases_`date +%Y%m%d`.sql

/usr/local/mysql/bin/mysql_upgrade -uroot -p123456 //执行表权限升级

/usr/local/mysql/bin/mysqlcheck --all-databases -uroot -p123456 //检查所有数据库 , 这一步的密码已是旧数据库的密码了

6、重启服务

/etc/init.d/mysqld restart

7、列出数据库

/usr/local/mysql/bin/mysql -uroot -p123456

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| hive_db |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows in set (0.00 sec)

7、最终版本

# mysql -V

mysql Ver 14.14 Distrib 5.7.19, for linux-glibc2.12 (x86_64) using EditLine wrapper

四、回退方式

1、回退操作

假设新的MySQL-5.7.19起不来！

ps -ef | grep mysql

cd /usr/local/

unlink mysql

ln -s mysql-5.7.17-linux-glibc2.5-x86_64 mysql

[root@linuxyunwei local]# /etc/init.d/mysqld start

Starting MySQL.. SUCCESS!

2、查版本

方式一：

[root@linuxyunwei local]# mysql -V

mysql Ver 14.14 Distrib 5.7.17, for linux-glibc2.5 (x86_64) using EditLine wrapper

[root@linuxyunwei local]# mysql --help | head -n2

mysql Ver 14.14 Distrib 5.7.17, for linux-glibc2.5 (x86_64) using EditLine wrapper

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

方式二：

mysql> select version();

+-----------+

| version() |

+-----------+

| 5.7.17 |

+-----------+

1 row in set (0.09 sec)

mysql>

方式三：

mysql> status

--------------

mysql Ver 14.14 Distrib 5.7.17, for linux-glibc2.5 (x86_64) using EditLine wrapper

Connection id: 3

Current database:

Current user: root@linuxyunwei

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.7.17 MySQL Community Server (GPL)

Protocol version: 10

Connection: linuxyunwei via UNIX socket

Server characterset: latin1

Db characterset: latin1

Client characterset: utf8

Conn. characterset: utf8

UNIX socket: /tmp/mysql.sock

Uptime: 1 min 14 sec

Threads: 1 Questions: 6 Slow queries: 0 Opens: 105 Flush tables: 1 Open tables: 98 Queries per second avg: 0.081

--------------