MYSQL 實時升級

（MYSQL 升級實驗內容）

步驟如下：
準備一臺新的服務器，安裝mysql5.7 ,再把v5.7 mysql 添加到以v5.6 mysql 為主節點的主從架構集群中。
再進行主從切換。設置成mysql5.7 為主節點，這樣就完成了MYSQL 的升級。

1、mysql 5.7.17 安裝

2、配置成主從架構

3、主從節點切換

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

1.1.建立安裝目錄：

?? ?[root@mdw ~]# mkdir /opt/mysql/data /opt/mysql/log
?? ?[root@mdw ~]# chown mysql:mysql /opt/mysql/data /opt/mysql/log
?? ?[root@mdw ~]# ll /opt/mysql
?? ?total 8
?? ?drwxr-xr-x 2 mysql mysql 4096 Feb 23 18:34 data
?? ?drwxr-xr-x 2 mysql mysql 4096 Feb 23 18:34 log

1.2.下載

?? ?安裝支持庫
?? ??? ?yum install gcc gcc-c++ -y
?? ??? ?yum install -y ncurses-devel.x86_64
?? ??? ?yum install -y cmake.x86_64
?? ??? ?yum install -y libaio.x86_64
?? ??? ?yum install -y bison.x86_64
?? ??? ?yum install -y gcc-c++.x86_64


?? ?下載mysql 5.7

?? ?wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
?? ?tar zxvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
?? ?cd mysql-5.7.17-linux-glibc2.5-x86_64


?? ?bin/mysqld --initialize --user=mysql --basedir=basedir=/opt/mysql/mysql5.7.17 --datadir=/opt/mysql/data ?

?? ?vi /opt/mysql/mysql5.7.17/support-files/my-default.cnf
?? ? basedir = /opt/mysql/mysql5.7.17
?? ? datadir = /opt/mysql/data
?? ? port = 3310
?? ? server_id = 201710
?? ? socket = /tmp/mysql3310.sock



?? ?[root@sdw2 mysql5.7.17]# bin/mysqld --initialize --user=mysql --basedir=basedir=/opt/mysql/mysql5.7.17 --datadir=/opt/mysql/data
?? ?2017-02-24T08:32:50.780034Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
?? ?2017-02-24T08:32:50.796889Z 0 [ERROR] Can't find error-message file 'basedir=/opt/mysql/mysql5.7.17/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
?? ?2017-02-24T08:32:52.153749Z 0 [Warning] InnoDB: New log files created, LSN=45790
?? ?2017-02-24T08:32:52.463255Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
?? ?2017-02-24T08:32:52.558447Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d521c809-fa6b-11e6-9a40-000c291f5cce.
?? ?2017-02-24T08:32:52.567667Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
?? ?2017-02-24T08:32:52.568642Z 1 [Note] A temporary password is generated for root@localhost: 8qdO.tWgl,eJ
?? ?[root@sdw2 mysql5.7.17]#

?? ?#修改 mysql.server，拷貝到系統目錄中，后面可以使用mysql.server 來啟動
?? ?[root@sdw2 mysql5.7.17]# vi support-files/mysql.server
?? ?[root@sdw2 mysql5.7.17]# cp support-files/mysql.server /etc/init.d/mysql
?? ?[root@sdw2 mysql5.7.17]#


?? ?#啟動實例
?? ?[root@sdw2 mysql5.7.17]# bin/mysqld_safe --user=mysql &
?? ?[1] 12880
?? ?[root@sdw2 mysql5.7.17]# 2017-02-24T08:36:28.391236Z mysqld_safe Logging to '/opt/mysql/data/sdw2.err'.
?? ?Logging to '/opt/mysql/data/sdw2.err'.
?? ?2017-02-24T08:36:28.475157Z mysqld_safe Starting mysqld daemon with databases from /opt/mysql/data

?? ?[root@sdw2 mysql5.7.17]# ps -ef|grep mysqld
?? ?root???? 12880 12581? 0 00:36 pts/1??? 00:00:00 /bin/sh bin/mysqld_safe --user=mysql
?? ?mysql??? 13039 12880? 8 00:36 pts/1??? 00:00:01 ./bin/mysqld --basedir=/opt/mysql/mysql5.7.17 --datadir=/opt/mysql/data --plugin-dir=/opt/mysql/mysql5.7.17/lib/plugin --user=mysql --log-error=/opt/mysql/data/sdw2.err --pid-file=/opt/mysql/data/sdw2.pid --socket=/tmp/mysql3310.sock --port=3310
?? ?root???? 13072 12581? 0 00:36 pts/1??? 00:00:00 grep mysqld
?? ?[root@sdw2 mysql5.7.17]#


?? ??? ?
?? ?修改密碼?? ?
?? ?bin/mysqladmin -u root password "sa123" -S /tmp/mysql3310.sock


?? ?#登錄驗證
?? ?[root@sdw2 ~]# /opt/mysql/mysql5.7.17/bin/mysql -uroot -p -S /tmp/mysql3310.sock
?? ?Enter password:
?? ?Welcome to the MySQL monitor.? Commands end with ; or \g.
?? ?Your MySQL connection id is 6
?? ?Server version: 5.7.17 MySQL Community Server (GPL)

?? ?Copyright (c) 2000, 2016, 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>? show variables like 'server_id';
?? ?+---------------+--------+
?? ?| Variable_name | Value? |
?? ?+---------------+--------+
?? ?| server_id???? | 201710 |
?? ?+---------------+--------+
?? ?1 row in set (0.05 sec)

?? ?mysql>


?? ?到此 5.7 版本MYSQL 安裝完成

2.1?? ?備份5.6 mysql，恢復數據到5.7mysql

?? ?2.1.1 建立備份用戶

?? ?
?? ?mysql> grant replication slave,replication client on *.* to 'repl'@'%' identified by 'repl123';
?? ?Query OK, 0 rows affected (0.13 sec)

?? ?mysql>
?? ??? ?

?? ?2.2.2 備份數據

?? ?[root@mdw ~]# /usr/local/mysql/bin/mysqldump --single-transaction --master-data=2 -uroot -p? -S /tmp/mysql3306.sock? --all-databases > ~/bak20170224
?? ?Enter password:

?? ?[root@mdw ~]# ll bak* -h
?? ?-rw-r--r-- 1 root root 641K Feb 24 01:00 bak20170224
?? ?[root@mdw ~]#

?? ?2.2.3 拷貝文件到v5.7mysql 服務器中

?? ?[root@mdw ~]# scp bak20170224 root@192.168.2.236:/root/bak201724
?? ?root@192.168.2.236's password:
?? ?bak20170224??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? 100%? 640KB 640.3KB/s?? 00:00?? ?
?? ?[root@mdw ~]#

?? ?2.2.4 恢復數據庫

?? ?mysql>? show variables like 'server_id';
?? ?+---------------+--------+
?? ?| Variable_name | Value? |
?? ?+---------------+--------+
?? ?| server_id???? | 201710 |
?? ?+---------------+--------+
?? ?1 row in set (0.05 sec)

?? ?mysql> source /root/bak20170224
?? ?ERROR:
?? ?Failed to open file '/root/bak20170224', error: 2
?? ?mysql> source /root/bak201724
?? ?Query OK, 0 rows affected (0.00 sec)
?? ?........
?? ?Query OK, 0 rows affected (0.00 sec)

?? ?mysql> show databases;
?? ?+--------------------+
?? ?| Database?????????? |
?? ?+--------------------+
?? ?| information_schema |
?? ?| erp??????????????? |
?? ?| jfedu????????????? |
?? ?| mysql????????????? |
?? ?| performance_schema |
?? ?| sys??????????????? |
?? ?| test?????????????? |
?? ?+--------------------+
?? ?7 rows in set (0.00 sec)

?? ?#看到數據庫已復制過來了。

?? ?

?? ?2.3 配置主節點

?? ? change master to master_host='192.168.2.220',
?? ? master_port=3306,
?? ? master_user='repl',
?? ? master_password='repl123',
?? ? master_log_file='mysql-bin.000002',
?? ? master_log_pos=660824,
?? ? master_connect_retry=30;


?? ??? ?master_log_file 參數值可以從 備份文件中查找到(vim /root/bak20170222 )

?? ?2.3.1 啟動從節點

?? ?
?? ??? ?start slave;

?? ?2.3.2 確認同步狀態

?? ??? ?mysql> show slave status\G;
?? ??? ?*************************** 1. row ***************************
?? ??? ??? ??? ??? ??? Slave_IO_State: Waiting for master to send event
?? ??? ??? ??? ??? ??? ?? Master_Host: 192.168.2.220
?? ??? ??? ??? ??? ??? ?? Master_User: repl
?? ??? ??? ??? ??? ??? ?? Master_Port: 3306
?? ??? ??? ??? ??? ??? ?Connect_Retry: 30
?? ??? ??? ??? ??? ?? Master_Log_File: mysql-bin.000002
?? ??? ??? ??? ?? Read_Master_Log_Pos: 660824
?? ??? ??? ??? ??? ??? Relay_Log_File: sdw2-relay-bin.000002
?? ??? ??? ??? ??? ??? ?Relay_Log_Pos: 317
?? ??? ??? ??? ?Relay_Master_Log_File: mysql-bin.000002
?? ??? ??? ??? ??? ? 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: 660824
?? ??? ??? ??? ??? ?? Relay_Log_Space: 523
?? ??? ??? ??? ??? ?? 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: 201701
?? ??? ??? ??? ??? ??? ?? Master_UUID: 746786d5-f7fb-11e6-a3e8-000c29601c12
?? ??? ??? ??? ??? ? Master_Info_File: /opt/mysql/data/master.info
?? ??? ??? ??? ??? ??? ??? ?SQL_Delay: 0
?? ??? ??? ??? ?? SQL_Remaining_Delay: NULL
?? ??? ??? ?? Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
?? ??? ??? ??? ??? 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
?? ??? ??? ??? ? Replicate_Rewrite_DB:
?? ??? ??? ??? ??? ??? ? Channel_Name:
?? ??? ??? ??? ??? Master_TLS_Version:
?? ??? ?1 row in set (0.00 sec)

?? ??? ?ERROR:
?? ??? ?No query specified

?? ?2.3.3 驗證主從架構是否正常

mysql> status slave;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'status slave' at line 1
mysql> show slave status\G;
*************************** 1. row ***************************
?????????????? Slave_IO_State: Waiting for master to send event
????????????????? Master_Host: 192.168.2.220
????????????????? Master_User: repl
????????????????? Master_Port: 3306
??????????????? Connect_Retry: 30
????????????? Master_Log_File: mysql-bin.000002
????????? Read_Master_Log_Pos: 660824
?????????????? Relay_Log_File: sdw2-relay-bin.000002
??????????????? Relay_Log_Pos: 317
??????? Relay_Master_Log_File: mysql-bin.000002
???????????? 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: 660824
????????????? Relay_Log_Space: 523
????????????? 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: 201701
????????????????? Master_UUID: 746786d5-f7fb-11e6-a3e8-000c29601c12
???????????? Master_Info_File: /opt/mysql/data/master.info
??????????????????? SQL_Delay: 0
????????? SQL_Remaining_Delay: NULL
????? Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
?????????? 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
???????? Replicate_Rewrite_DB:
???????????????? Channel_Name:
?????????? Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified

?? ?2.3.4 主節點插入數據

?? ??? ?mysql> use erp;
?? ??? ?Reading table information for completion of table and column names
?? ??? ?You can turn off this feature to get a quicker startup with -A

?? ??? ?Database changed
?? ??? ?mysql> show tables;
?? ??? ?+---------------+
?? ??? ?| Tables_in_erp |
?? ??? ?+---------------+
?? ??? ?| test1???????? |
?? ??? ?+---------------+
?? ??? ?1 row in set (0.00 sec)

?? ??? ?mysql> select * from test1;
?? ??? ?+------+---------------+
?? ??? ?| id?? | name????????? |
?? ??? ?+------+---------------+
?? ??? ?|??? 1 | test1???????? |
?? ??? ?|??? 2 | test2???????? |
?? ??? ?|??? 3 | test 3 update |
?? ??? ?|??? 3 | test 3 update |
?? ??? ?|??? 3 | test 3 update |
?? ??? ?+------+---------------+
?? ??? ?5 rows in set (0.00 sec)

?? ??? ?mysql> insert into test1 values(4,'test 4 end');
?? ??? ?Query OK, 1 row affected (0.17 sec)

?? ??? ?mysql> system ifconfig;
?? ??? ?eth0????? Link encap:Ethernet? HWaddr 00:0C:29:60:1C:12 ?
?? ??? ??? ??? ?? inet addr:192.168.2.220? Bcast:192.168.2.255? Mask:255.255.255.0

?? ??? ?2.3.5 從節點查詢數據

?? ??? ?mysql> show databases;
?? ??? ?+--------------------+
?? ??? ?| Database?????????? |
?? ??? ?+--------------------+
?? ??? ?| information_schema |
?? ??? ?| erp??????????????? |
?? ??? ?| jfedu????????????? |
?? ??? ?| mysql????????????? |
?? ??? ?| performance_schema |
?? ??? ?| sys??????????????? |
?? ??? ?| test?????????????? |
?? ??? ?+--------------------+
?? ??? ?7 rows in set (0.00 sec)


?? ??? ?mysql> use erp;
?? ??? ?Reading table information for completion of table and column names
?? ??? ?You can turn off this feature to get a quicker startup with -A

?? ??? ?Database changed
?? ??? ?mysql> select * from test1;
?? ??? ?+------+---------------+
?? ??? ?| id?? | name????????? |
?? ??? ?+------+---------------+
?? ??? ?|??? 1 | test1???????? |
?? ??? ?|??? 2 | test2???????? |
?? ??? ?|??? 3 | test 3 update |
?? ??? ?|??? 3 | test 3 update |
?? ??? ?|??? 3 | test 3 update |
?? ??? ?+------+---------------+
?? ??? ?5 rows in set (0.00 sec)

?? ??? ?mysql> select * from test1;
?? ??? ?+------+---------------+
?? ??? ?| id?? | name????????? |
?? ??? ?+------+---------------+
?? ??? ?|??? 1 | test1???????? |
?? ??? ?|??? 2 | test2???????? |
?? ??? ?|??? 3 | test 3 update |
?? ??? ?|??? 3 | test 3 update |
?? ??? ?|??? 3 | test 3 update |
?? ??? ?|??? 4 | test 4 end??? |
?? ??? ?+------+---------------+
?? ??? ?6 rows in set (0.00 sec)

?? ??? ?mysql> system ifconfig
?? ??? ?eth1????? Link encap:Ethernet? HWaddr 00:0C:29:1F:5C:CE ?
?? ??? ??? ??? ?? inet addr:192.168.2.236? Bcast:192.168.2.255? Mask:255.255.255.0
?? ??? ??? ??? ?? inet6 addr: fe80::20c:29ff:fe1f:5cce/64 Scope:Link
?? ??? ??? ??? ?? UP BROADCAST RUNNING MULTICAST? MTU:1500? Metric:1

?? ?3.主從切換

?? ?3.1 設置主節點為只讀?? ?

?? ?mysql> show variables like 'read_only';
?? ?+---------------+-------+
?? ?| Variable_name | Value |
?? ?+---------------+-------+
?? ?| read_only???? | OFF?? |
?? ?+---------------+-------+
?? ?1 row in set (0.00 sec)

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

?? ?mysql> show variables like 'read_only';
?? ?+---------------+-------+
?? ?| Variable_name | Value |
?? ?+---------------+-------+
?? ?| read_only???? | ON??? |
?? ?+---------------+-------+
?? ?1 row in set (0.00 sec)

?? ?mysql>

?? ?3.2 應用連接到從節點

?? ?只要應用連接數據庫的字串，修改為： 3310 這個實例即可

?? ?到此mysql 升級已完成

總結

以上是生活随笔為你收集整理的MYSQL 实时升级的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。