mysql主从搭建切换
发布时间：2022-03-25 14:14:45 所属栏目：MySql教程 来源：互联网
导读：环境： 172.30.249.156 percona5.6.27 主机名：tr-mysql 跑着生产环境的应用的虚拟机 192.168.129.153 percona5.6.27 主机名：trcloud-gtt-db 新申请的物理机 工作内容：给生产环境数据库搭建主从架构，并将主库放到新申请的物理机上,连续停机不能超过10分钟
|
环境： 172.30.249.156 percona5.6.27 主机名：tr-mysql 跑着生产环境的应用的虚拟机 192.168.129.153 percona5.6.27 主机名：trcloud-gtt-db 新申请的物理机 工作内容：给生产环境数据库搭建主从架构，并将主库放到新申请的物理机上,连续停机不能超过10分钟 大致流程：搭建主从-->主备切换-->测试结果，因为生产环境数据大概有100多G，考虑到停机时间和效率的问题，恢复备库使用Xtrabackup方式备份恢复，不使用mysqldump和冷备的方式 一：搭建主从 1、创建复制账号 在Master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。 命令如下： mysql> create user 'replicat'@'192.168.129.153' identified by 'passw'; Query OK, 0 rows affected (0.03 sec) mysql> grant replication slave,super on *.* to 'replicat'@'192.168.129.153'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 建立一个帐户backup，并且只能允许从10.211.55.5这个地址上来登陆，密码是passw。 2、配置master、slave参数 主库: 打开二进制日志、指定唯一server ID、 [root@localhost data]# vi /etc/my.cnf …… log-bin=/data/DB/mysql/mysql-bin server-id=1 ……. 备库: 打开二进制日志、指定唯一server ID [root@localhost mysql]# vi /etc/my.cnf …… log-bin=/data/DB/mysql/mysql-bin server-id=2 ……. 3、拷贝并恢复备库数据 主库备份： [root@tr-mysql backup]# xtrabackup --backup --user=root --datadir=/data/DB/mysql/ --target-dir=/data/backup/20160628bak/ ........ 160628 09:55:17 Executing UNLOCK TABLES 160628 09:55:17 All tables unlocked 160628 09:55:17 Backup created in directory '/data/backup/20160628bak/' 160628 09:55:17 [00] Writing backup-my.cnf 160628 09:55:17 [00] ...done 160628 09:55:18 [00] Writing xtrabackup_info 160628 09:55:18 [00] ...done xtrabackup: Transaction log of lsn (1696091795) to (1696091805) was copied. 160628 09:55:18 completed OK! [root@tr-mysql backup]# xtrabackup --prepare --target-dir=/data/backup/20160628bak/ --让备份数据一致 [root@tr-mysql backup]# scp 20160628bak/* root@192.168.129.153:/data/backup/20160628bak/ 备库恢复： [root@trcloud-gtt-db ~]# rsync -avrp /data/backup/20160628bak/ /data/DB/mysql/ ........ sent 6426459950 bytes received 259 bytes 197737237.20 bytes/sec total size is 6425674808 speedup is 1.00 [root@trcloud-gtt-db~ ]# chown -R mysql:mysql /data/DB/mysql 4、主库准备测试数据 mysql> use test; mysql> create table id(id int(10)); Query OK, 0 rows affected (1.02 sec) mysql> insert into id values(1); Query OK, 1 row affected (0.14 sec) 5、设置复制进程 查看master当前binlog文件 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 834 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) slave创建复制进程 mysql> change master to master_host='172.30.249.156', -> master_user='replicat', -> master_password='passw', -> master_log_file='mysql-bin.000002', -> master_log_pos=0; 6、启动slave，查看进程 启动 mysql> start slave; Query OK, 0 rows affected (0.03 sec) 查看slave进程 mysql> show slave statusG; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.30.249.156 Master_User: replicat Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 834 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 997 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: 834 Relay_Log_Space: 1171 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: 7d7ee32c-26cd-11e6-8fe2-fa163ecb3078 Master_Info_File: /data/DB/mysql/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) 在这里主要是看: Slave_IO_Running=Yes Slave_IO_Running=Yes Slave_SQL_Running=Yes slave的I/O和SQL线程都已经开始运行，而且Seconds_Behind_Master不再是NULL。日志的位置增加了，意味着一些事件被获取并执行了。如果你在master上进行修改，你可以在slave上看到各种日志文件的位置的变化，同样，你也可以看到数据库中数据的变化。 查看master进程 mysql> show processlistG; ........ *************************** 3. row *************************** Id: 4 User: canal Host: 172.30.248.95:35966 db: NULL Command: Binlog Dump Time: 11821 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL Rows_sent: 0 Rows_examined: 0 ........ *************************** 5. row *************************** Id: 12 User: replicat Host: 192.168.129.153:51948 db: NULL Command: Binlog Dump Time: 614 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL Rows_sent: 0 Rows_examined: 0 7、测试结果 slave上查看创建的表和数据都有了 mysql> select * from id; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec) 在master上修改数据 mysql> update id set id=2 where id =1; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 查看slave数据 mysql> select * from id; +------+ | id | +------+ | 2 | +------+ 1 row in set (0.00 sec) 测试通过 二：主从切换 1、在从库（新的主服务器）上创建用户 mysql> create user 'replicat'@'172.30.249.156' identified by 'passw'; Query OK, 0 rows affected (0.03 sec) mysql> grant replication slave,super on *.* to 'replicat'@'172.30.249.156'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 2、从库（新的主服务器） 操作 mysql> STOP SLAVE IO_THREAD; Query OK, 0 rows affected (0.03 sec) mysql> SHOW PROCESSLIST; +----+-----------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +----+-----------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+-----------+---------------+ | 1 | event_scheduler | localhost | NULL | Daemon | 2238 | Waiting on empty queue | NULL | 0 | 0 | | 5 | root | localhost | test | Query | 0 | init | SHOW PROCESSLIST | 0 | 0 | | 7 | system user | | NULL | Connect | 809 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | 0 | 0 | +----+-----------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+-----------+---------------+ 确保状态为：has read all relay log mysql> show slave status G； *************************** 1. row *************************** Slave_IO_State: Master_Host: 172.30.249.156 Master_User: replicat Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1523 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 1686 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: No 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: 1523 Relay_Log_Space: 1860 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: NULL 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: 7d7ee32c-26cd-11e6-8fe2-fa163ecb3078 Master_Info_File: /data/DB/mysql/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 3、从库变主库 mysql> STOP SLAVE; Query OK, 0 rows affected (0.03 sec) mysql> RESET MASTER; Query OK, 0 rows affected (0.12 sec) mysql> RESET SLAVE; Query OK, 0 rows affected (0.11 sec) mysql> show master status G *************************** 1. row *************************** File: mysql-bin.000009 Position: 120 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) 4、主库变从库 mysql> RESET MASTER; Query OK, 0 rows affected (3.36 sec) mysql> RESET SLAVE; Query OK, 0 rows affected (0.00 sec) 设置slave进程 mysql> change master to master_host='192.168.129.153', -> master_user='replicat', -> master_password='passw', -> master_log_file='mysql-bin.000009', -> master_log_pos=120; 启动slave进程 mysql> start slave; 5、修改参数并重启 将从库变成只读 修改现在的从库参数 read-only =1 innodb_read_only =1 super_read_only =1 #（拥有super权限也无法修改） 重启 [root@tr-mysql mysql]# service mysql restart 6、检查主从是否都正常 主库 SHOW PROCESSLIST; show master status G 从库 SHOW PROCESSLIST; start slave; show slave status G 7、测试数据 主库 mysql> update id set id=1 where id=2; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 从库 mysql> select * from id; +------+ | id | +------+ | 1 | +------+ 切换成功 （编辑：威海站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
