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 > ~/bak20170224Enter 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 升级已完成