mysql5.5.46升级到Percona Server 5.6.27

1.升级前的准备

查看本机openssl版本

root@DB-Slave mysql5.6]# rpm -qa | grep ssl

openssl-1.0.1e-42.el6_7.2.x86_64

openssl-devel-1.0.1e-42.el6_7.2.x86_64

https://www.percona.com/downloads/Percona-Server-5.6/LATEST/软件下载地址

 Percona-Server-5.6.27-rel75.0-Linux.x86_64.ssl101.tar.gz的openssl就是1.0.1版本的

2.安装新的版本

旧的版本：Server version: 5.5.46-log MySQL Community Server (GPL)

新的版本：Server version: 5.6.27-75.0-log Percona Server (GPL), Release 75.0

新版本软件目录/app/mysql5.6

新版本数据目录/data/mysql3306

配置文件：/app/mysql5.6/my.cnf

 

[client]

socket=/app/mysql5.6/mysql.sock

default-character-set=utf8

port=3307

[mysql]

prompt=\\u@\\d \\r:\\m:\\s>

no-auto-rehash

[mysqld_safe]

log-error=/data/mysql3306/mysqld.error

[mysqld]

socket=/app/mysql5.6/mysql.sock

pid-file=/app/mysql5.6/mysqld.pid

basedir=/app/mysql5.6

datadir=/data/mysql3306

port=3307

server_id=83

character-set-server=utf8

skip-external-locking

skip-name-resolve

max_connections=1024

max_connect_errors=1000

wait_timeout =  400

interactive_timeout = 400

table_definition_cache=500

table_open_cache=500

sort_buffer_size = 16M

tmp_table_size = 200M

 

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_type=0

query_cache_size=0

thread_concurrency = 16

lower_case_table_names = 1

log_bin_trust_function_creators = 1

#################slow log####################

slow-query_log=1

slow-query_log_file=/app/mysql5.6/logs/mysql.slow

long_query_time=2

####################binlog######################

log-bin=mysql-bin

binlog-format=ROW

expire_logs_days=5

sync_binlog=1

################replication##########

log-slave-updates=1

################INNODB################

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

transaction-isolation=READ-COMMITTED

innodb_buffer_pool_size=10G

innodb_flush_log_at_trx_commit=2

innodb_strict_mode=1

innodb_flush_method=O_DIRECT

innodb_file_format=Barracuda

innodb_log_files_in_group=3

innodb_file_per_table=1

innodb_io_capacity=500

innodb_support_xa=1

innodb_additional_mem_pool_size=16M

innodb_log_buffer_size=64M

 

 

[mysqldump]

quick

max_allowed_packet=128M

myisam_max_sort_sort_file_size=2G

3.解压文件

tar zxvf Percona-Server-5.6.27-rel75.0-Linux.x86_64.ssl101.tar.gz

4.创建目录

mkdir /app

mkdir /data/mysql3306

mv Percona-Server-5.6.27-rel75.0-Linux.x86_64.ssl101 /app/mysql5.6

chown -R mysql:mysql /app/mysql5.6

chown mysql:mysql /data/mysql3306

5.初始化新版本

/app/mysql5.6/scripts/mysql_install_db --user=mysql --basedir=/app/mysql5.6 --datadir=/data/mysql3306   --defaults-file=/app/mysql5.6/my.cnf

 

6.修改配置

[client]

socket=/app/mysql5.6/mysql.sock

default-character-set=utf8

port=3307

[mysql]

prompt=\\u@\\d \\r:\\m:\\s>

no-auto-rehash

[mysqld_safe]

log-error=/data/mysql3306/mysqld.error

[mysqld]

socket=/app/mysql5.6/mysql.sock

pid-file=/app/mysql5.6/mysqld.pid

basedir=/app/mysql5.6

datadir=/data/mysql3306

port=3307

server_id=83

character-set-server=utf8

skip-external-locking

skip-name-resolve

max_connections=1024

max_connect_errors=1000

wait_timeout =  400

interactive_timeout = 400

table_definition_cache=500

table_open_cache=500

sort_buffer_size = 16M

tmp_table_size = 200M

 

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_type=0

query_cache_size=0

thread_concurrency = 16

lower_case_table_names = 1

log_bin_trust_function_creators = 1

#################slow log####################

slow-query_log=1

slow-query_log_file=/app/mysql5.6/logs/mysql.slow

long_query_time=2

####################binlog######################

log-bin=mysql-bin

binlog-format=ROW

expire_logs_days=5

sync_binlog=1

################replication##########

log-slave-updates=1

################INNODB################

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

transaction-isolation=READ-COMMITTED

innodb_buffer_pool_size=10G

innodb_flush_log_at_trx_commit=2

innodb_strict_mode=1

innodb_flush_method=O_DIRECT

innodb_file_format=Barracuda

innodb_log_files_in_group=3

innodb_file_per_table=1

innodb_io_capacity=500

innodb_support_xa=1

innodb_additional_mem_pool_size=16M

innodb_log_buffer_size=64M

 

 

[mysqldump]

quick

max_allowed_packet=128M

myisam_max_sort_sort_file_size=2G

7.修改启动脚本

[root@DB-master ~]# cp /app/mysql5.6/support-files/mysql.server /etc/init.d/mysqld3307

[root@DB-master ~]# vi  /etc/init.d/mysqld3307

….

…..

basedir=/app/mysql5.6

datadir=/data/mysql3306

…..

…

[root@DB-master ~]# chmod +x /etc/init.d/mysqld3307

启动新版本

[root@DB-master ~]# /etc/init.d/mysqld3307 start

Starting MySQL (Percona Server)... SUCCESS!

8.旧版本数据导出，导入新版本

导出

mysqldump -uroot -p123456 --socket=/var/lib/mysql/mysql.sock  --max_allowed_packet=1048576 --net_buffer_length=16384  --default-character-set=utf8 --all-databases --single-transaction --routines --triggers --events --master-data=2 >all2016114.sql

 

[root@DB-master ~]# more all2016114.sql

….

….

-- CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000035', MASTER_LOG_POS=19461006;

注意：主从同步需要

…..

….

导入

mysql -uroot -p123456 --socket=/app/mysql5.6/mysql.sock < all2016114.sql

 

9.执行mysql_upgrade

注意一定要执行可以让旧的数据有新的版本功能

/app/mysql5.6/bin/mysql_upgrade -uroot -p123456 --socket=/app/mysql5.6/mysql.sock

 

10.主从搭建

旧版本

[root@DB-master mysql3306]# mysql -uroot -p123456

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4622

Server version: 5.5.46-log MySQL Community Server (GPL)

 

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

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql> GRANT REPLICATION  SLAVE ON *.*  TO  'repl'@'192.168.1.%'  IDENTIFIED BY 'repl';

Query OK, 0 rows affected (0.11 sec)

 

新版本

[root@DB-master mysql3306]#/app/mysql5.6/bin/mysql -uroot -p123456  --socket=/app/mysql5.6/mysql.sock

Warning: Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 6

Server version: 5.6.27-75.0-log Percona Server (GPL), Release 75.0, Revision 8bb53b6

 

Copyright (c) 2009-2015 Percona LLC and/or its affiliates

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

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.81',MASTER_PORT=3306,MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='master-bin.000035',MASTER_LOG_POS=19461006;

mysql> start slave;

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.1.81

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: master-bin.000037

          Read_Master_Log_Pos: 16412

               Relay_Log_File: DB-master-relay-bin.000005

                Relay_Log_Pos: 16572

        Relay_Master_Log_File: master-bin.000037

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 16412

              Relay_Log_Space: 16789

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 1

                  Master_UUID:

             Master_Info_File: /data/mysql3306/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

1 row in set (0.00 sec)

 

ERROR:

No query specified

 

 

11.升级切换

把主库停止，把从库停止后，端口修改成3306，因为为了对生产库没有影响。在停止时必须和应用联系。

停止主库

[root@DB-master mysql3306]#/etc/init.d/mysql stop

Shutting down MySQL....                                             done

停止从库

[root@DB-master ~]# /etc/init.d/mysqld3307 stop

Shutting down MySQL (Percona Server).... SUCCESS!

修改启动

[root@DB-master ~]#  cd /etc/init.d/

[root@DB-master ~]# mv mysql mysqlold

[root@DB-master ~]# mv mysql3307 mysql

 

 

修改配置文件

[root@DB-master ~]#/etc/init.d # vi/app/mysql5.6/my.cnf

 

[client]

….

port=3306

…

 [mysqld]

…………

port=3306

……

 

 

最后启动新版本

[root@DB-master ~]# /etc/init.d/mysql start

Starting MySQL (Percona Server)... SUCCESS!  

 

清除同步

mysql> resetslave all;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show slave status\G;

Empty set (0.00 sec)

 

ERROR:

No query specified