MySQL 5.5 主主复制搭建流程

刺骨的言语ヽ痛彻心扉 2024-04-17 23:56 5阅读 0赞

\--节点1 IP 192.168.78.141  PORT 3306  
 \--节点2 IP 192.168.78.137  PORT 5505  
  
**--配置节点1到节点2的复制**  
 \--编辑节点1的配置文件  
 \[root@localhost install\]\# vim /etc/my.cnf  
 \# Log  
 server-id = 100  
 log-bin = /log/binlog/mysql-bin  
  
 \--在节点2安装好MySQL软件，安装流程可以参考源码安装文章  
 http://blog.itpub.net/26506993/viewspace-2072859/  
  
 \--在节点1，使用Xtrabackup创建完整备份  
 关于Xtrabackup，可参考  
 http://blog.itpub.net/26506993/viewspace-2087734/  
 http://blog.itpub.net/26506993/viewspace-2088737/  
  
 \[root@localhost backup\]\# /install/percona/percona-xtrabackup-2.4.2-Linux-x86\_64/bin/innobackupex  --defaults-file=/etc/my.cnf --stream=tar /tmp --user system --password 'Mysql\#2015' | gzip ->   
 /backup/xtra/xtra\_fullbackup\_20160501.tar.gz  
  
 \--拷贝备份到节点2  
 \[root@localhost backup\]\# scp /backup/xtra/xtra\_fullbackup\_20160501.tar.gz  root@192.168.78.137:/backup/20160501  
  
 解压备份到数据文件目录  
 \[root@localhost 5505\]\# tar xivfz xtra\_fullbackup\_20160501.tar.gz -C /mysql\_data/5505  
  
 在节点2上面需要安装Xtraback  
 \--使用Xtrabackup准备数据、应用日志，使数据文件达到一致性的状态  
 \[root@localhost bin\]\# ./innobackupex --defaults-file=/mysql\_data/cnf/my.cnf  --apply-log /mysql\_data/5505  
 .....  
 InnoDB: Doing recovery: scanned up to log sequence number 68405269 (0%)  
 InnoDB: Doing recovery: scanned up to log sequence number 68405269 (0%)  
 InnoDB: Database was not shutdown normally!  
 InnoDB: Starting crash recovery.  
 InnoDB: xtrabackup: Last MySQL binlog file position 414, file name /log/binlog/mysql-bin.000012  
 InnoDB: Removed temporary tablespace data file: "ibtmp1"  
 InnoDB: Creating shared tablespace for temporary tables  
 InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...  
 InnoDB: File './ibtmp1' size is now 12 MB.  
 InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.  
 InnoDB: 32 non-redo rollback segment(s) are active.  
 InnoDB: 5.7.11 started; log sequence number 68405269  
 xtrabackup: starting shutdown with innodb\_fast\_shutdown = 1  
 InnoDB: FTS optimize thread exiting.  
 InnoDB: Starting shutdown...  
 InnoDB: Shutdown completed; log sequence number 68405621  
 160430 23:51:25 completed OK!  
  
 \--在节点1数据库上面创建复制专用账户  
 mysql> grant replication slave on \*.\* to 'repl'@'192.168.78.%' identified by 'Mysql\#2015';  
 Query OK, 0 rows affected (0.04 sec)  
  
 \--配置节点2的配置文件  
 \[root@localhost 5505\]\# vim /mysql\_data/cnf/my.cnf  
 \# Log  
 server-id = 200  
 log-bin = /mysql\_log/binlog/mysql-bin  
 relay-log = /mysql\_log/binlog/product-relay-bin  
 relay-log-index = /mysql\_log/binlog/product-relay-index  
 binlog\_cache\_size = 32M  
 max\_binlog\_cache\_size = 512M  
 max\_binlog\_size = 512M  
 binlog\_format = MIXED  
  
 \--启动节点2的Mysql服务  
 \[root@localhost bin\]\# /data/bin/mysqld\_safe --defaults-file=/mysql\_data/cnf/my.cnf &  
  
 \--配置Slave节点复制环境  
 查询Slave节点连接Master节点的二进制文件和位置  
 使用Xtrabackup备份时，在xtrabackup\_binlog\_info文件中会保存这部分信息  
 \[root@localhost 5505\]\# more xtrabackup\_binlog\_info  
 mysql-bin.000012 414  
  
 \--在节点2执行CHANGE MASTER语句  
 mysql> change master to  
     -> master\_host='192.168.78.141',  
     -> master\_port=3306,  
     -> master\_user='repl',  
     -> master\_password='Mysql\#2015',  
     -> master\_log\_file='mysql-bin.000012',  
     -> master\_log\_pos=414;  
 Query OK, 0 rows affected (0.13 sec)  
  
 \--启动应用线程  
 mysql> start slave;  
  
 \--查看同步状态  
 mysql> show slave status\\G  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                Slave\_IO\_State: Waiting for master to send event  
                   Master\_Host: 192.168.78.141  
                   Master\_User: repl  
                   Master\_Port: 3306  
                 Connect\_Retry: 60  
               Master\_Log\_File: mysql-bin.000013  
           Read\_Master\_Log\_Pos: 341  
                Relay\_Log\_File: product-relay-bin.000003  
                 Relay\_Log\_Pos: 487  
         Relay\_Master\_Log\_File: mysql-bin.000013  
              Slave\_IO\_Running: Yes  
             Slave\_SQL\_Running: Yes  
  
 **--搭建节点2到节点1的复制**   
 \--在节店1的配置文件中，增加中继日志的设置  
 \[root@localhost log\]\# vim /etc/my.cnf  
 relay-log = /log/binlog/product-relay-bin  
 relay-log-index = /log/binlog/product-relay-index  
  
 \--重启节点1的数据库  
 \[root@localhost tmp\]\# /software/bin/mysqladmin -usystem -p'system' shutdown  
 160512 02:47:54 mysqld\_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended  
 \[1\]+  Done                    /software/bin/mysqld\_safe --defaults-file=/etc/my.cnf  (wd: ~)  
 (wd now: /tmp)  
 \[root@localhost tmp\]\# /software/bin/mysqld\_safe --defaults-file=/etc/my.cnf &  
 \[1\] 40246  
 \[root@localhost tmp\]\# 160512 02:48:13 mysqld\_safe Logging to '/log/err.log'.  
 160512 02:48:13 mysqld\_safe Starting mysqld daemon with databases from /data  
  
 mysql> show variables like 'relay\_log%';  
 \+-----------------------+---------------------------------+  
 | Variable\_name         | Value                           |  
 \+-----------------------+---------------------------------+  
 | relay\_log             | /log/binlog/product-relay-bin   |  
 | relay\_log\_index       | /log/binlog/product-relay-index |  
 | relay\_log\_info\_file   | relay-log.info                  |  
 | relay\_log\_purge       | ON                              |  
 | relay\_log\_recovery    | OFF                             |  
 | relay\_log\_space\_limit | 0                               |  
 \+-----------------------+---------------------------------+  
 6 rows in set (0.00 sec)  
  
\--在节点1上面增加全局只读锁，如果应用只连接到一个节点，如节点1，这一步可以忽略  
 mysql> flush tables with read lock;  
 Query OK, 0 rows affected (0.00 sec)  
  
 \--查看节点2当前的日志名称和位置，用于下面在节点1的change master to命令  
 mysql> show master status;  
 \+------------------+----------+--------------+------------------+  
 | File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB |  
 \+------------------+----------+--------------+------------------+  
 | mysql-bin.000006 | 14078491 |              |                  |  
 \+------------------+----------+--------------+------------------+  
 1 row in set (0.00 sec)  
  
 \--在节点1执行CHANGE MASTER语句  
 mysql> change master to  
     -> master\_host='192.168.78.137',  
     -> master\_port=5505,  
     -> master\_user='repl',  
     -> master\_password='Mysql\#2015',  
     -> master\_log\_file='mysql-bin.000006',  
     -> master\_log\_pos=14078491;  
 Query OK, 0 rows affected (0.13 sec)  
  
 \--启动应用线程  
 mysql> start slave;  
  
 \--节点1释放全局只读锁  
 mysql> unlock tables;  
 Query OK, 0 rows affected (0.00 sec)  
  
 \--查看同步状态  
 mysql> show slave status\\G  
 \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
                Slave\_IO\_State: Waiting for master to send event  
                   Master\_Host: 192.168.78.137  
                   Master\_User: repl  
                   Master\_Port: 5505  
                 Connect\_Retry: 60  
               Master\_Log\_File: mysql-bin.000006  
           Read\_Master\_Log\_Pos: 14078491  
                Relay\_Log\_File: product-relay-bin.000002  
                 Relay\_Log\_Pos: 253  
         Relay\_Master\_Log\_File: mysql-bin.000006  
              Slave\_IO\_Running: Yes  
             Slave\_SQL\_Running: Yes

在两个节点的配置文件中增加序列的相关参数，以避免生成的序列相同而产生冲突  
\--节点1

节点1上序列从1开始，增长值为2，即为奇数，如1、3、5

\#\# set this to server-id value

auto\_increment\_offset = 1

\#\# set this to the number of mysql servers

auto\_increment\_increment = 2

\--节点2  
节点2上序列从2开始，增长值为2，即为奇数，如2、4、6

\#\# set this to server-id value

auto\_increment\_offset = 2

\#\# set this to the number of mysql servers

auto\_increment\_increment = 2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2098740/，如需转载，请注明出处，否则将追究法律责任。