MySQL集群

╰半夏微凉° 2022-04-15 04:41 252阅读 0赞

# **MySQL集群** #

MySQL主从复制MySQL双主复制

实现方式：基于GTID的多源复制（MySQL5.7版本才有的特性）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1JhaW5feGlhb2xvbmc_size_16_color_FFFFFF_t_70][]

**192.168.1.94****服务器中操作：**

<table> 
 <tbody> 
 <tr> 
 <td> [root@server1 ~]# vim /etc/my.cnf [mysqld] #bin-log 开启二进制日志 server_id=94 log_bin=mysql-bin ## GTID gtid_mode=ON enforce_gtid_consistency=true </td> 
 </tr> 
 </tbody> 
</table>

重启MySQL使配置文件生效

<table> 
 <tbody> 
 <tr> 
 <td> [root@server1 ~]# systemctl restart mysqld [root@server1 ~]# ss -antp | grep 3306 #确保端口启动成功 LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=7697,fd=32)) mysql&gt; show master status; #查看二进制日志是否开启 +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql&gt; select @@server_uuid; #查看GTID的UUID +--------------------------------------+ | @@server_uuid | +--------------------------------------+ | e073964e-eba4-11e8-9eb4-000c2978fbe2 | +--------------------------------------+ 1 row in set (0.00 sec) </td> 
 </tr> 
 </tbody> 
</table>

创建一个数据同步用户

如果是已经使用过一段时间的数据库，先把所有的数据入到数据库中，本文中的数据库是没有进行使用。

<table> 
 <tbody> 
 <tr> 
 <td> 导出数据 mysqldump --user=root --password=Long110427. --flush-logs --master-data=2 --lock-all-tables --all-databases &gt; /tmp/aa.sql scp /tmp/aa.sql 192.168.1.95<a href="http://17/tmp/aa.sql" rel="nofollow">:/tmp/aa.sql</a> scp /tmp/aa.sql 192.168.1.96<a href="http://18/tmp/aa.sql" rel="nofollow">:/tmp/aa.sql</a> 导入数据 mysql&gt;&nbsp;SOURCE /tmp/aa.sql </td> 
 </tr> 
 </tbody> 
</table>

**192.168.1.95****服务器中的操作**

开启二进制日志，启动GTID。

<table> 
 <tbody> 
 <tr> 
 <td> [root@server2 ~]# vim /etc/my.cnf [mysqld] server_id=95 log_bin=mysql-bin gtid_mode=on enforce_gtid_consistency=true </td> 
 </tr> 
 </tbody> 
</table>

重启MySQL使配置文件生效

连接192.168.1.94服务器的数据库

<table> 
 <tbody> 
 <tr> 
 <td> mysql&gt; CHANGE MASTER TO -&gt; MASTER_HOST='192.168.1.94', -&gt; MASTER_USER='cmzw', -&gt; MASTER_PASSWORD='Cmzw.2018', -&gt; MASTER_AUTO_POSITION=1; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql&gt; start slave; Query OK, 0 rows affected (0.00 sec) mysql&gt; show slave status \G; #确保slave中的IO线程和SQL线程是Yes（下面红色的部分，如果不是请查看错误日志） *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.94 Master_User: cmzw Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 447 Relay_Log_File: server2-relay-bin.000002 Relay_Log_Pos: 660 Relay_Master_Log_File: mysql-bin.000001 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: 447 Relay_Log_Space: 869 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: 94 Master_UUID: e073964e-eba4-11e8-9eb4-000c2978fbe2 Master_Info_File: /var/lib/mysql/<a href="http://master.info" rel="nofollow">master.info</a> 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: e073964e-eba4-11e8-9eb4-000c2978fbe2:1 Executed_Gtid_Set: e073964e-eba4-11e8-9eb4-000c2978fbe2:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) </td> 
 </tr> 
 </tbody> 
</table>

**返回192.168.1.94服务器进行连接192.168.1.95数据库。（因为是双主复制）**

注意：如果创建的用户是不是以192.168.1.%的权限，还需要创建一个可以连接192.168.1.95的账户

<table> 
 <tbody> 
 <tr> 
 <td> 连接192.168.1.95的数据库 mysql&gt; CHANGE MASTER TO -&gt; MASTER_HOST='192.168.1.95', -&gt; MASTER_USER='cmzw', -&gt; MASTER_PASSWORD='Cmzw.2018', -&gt; MASTER_AUTO_POSITION=1; Query OK, 0 rows affected, 2 warnings (0.02 sec) mysql&gt; start slave; Query OK, 0 rows affected (0.00 sec) mysql&gt; show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.95 Master_User: cmzw Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: server1-relay-bin.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000001 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: 154 Relay_Log_Space: 576 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: 95 Master_UUID: d56c691c-eba7-11e8-84e0-000c292db05e Master_Info_File: /var/lib/mysql/<a href="http://master.info" rel="nofollow">master.info</a> 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: e073964e-eba4-11e8-9eb4-000c2978fbe2:1 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) </td> 
 </tr> 
 </tbody> 
</table>

这里双主复制已经算是成功了！

**192.168.1.96****服务器中操作(配置从服务器)**

在MySQL的数据目录中有两个文件master.info relay\_log.info 文件，要把这两个文件写入到表中

修改配置文件并重新启动

<table> 
 <tbody> 
 <tr> 
 <td> [root@server3 ~]# vi /etc/my.cnf [mysqld] server_id=96 gtid_mode=ON enforce_gtid_consistency=true master_info_repository=TABLE relay_log_info_repository=TABLE [root@server3 ~]# systemctl restart mysqld [root@server3 ~]# ss -antp |grep 3306 LISTEN 0 80 :::3306 :::* users:(("mysqld",pid=74430,fd=30)) </td> 
 </tr> 
 </tbody> 
</table>

接下来进行做主从，192.168.1.96既是192.168.1.94的从服务器也是192.168.1.95的从服务器。

<table> 
 <tbody> 
 <tr> 
 <td> mysql&gt; CHANGE MASTER TO -&gt; MASTER_HOST='192.168.1.94', -&gt; MASTER_USER='cmzw', -&gt; MASTER_PASSWORD='Cmzw.2018', -&gt; MASTER_AUTO_POSITION=1 FOR CHANNEL "master2"; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql&gt; CHANGE MASTER TO -&gt; MASTER_HOST='192.168.1.95', -&gt; MASTER_USER='cmzw', -&gt; MASTER_PASSWORD='Cmzw.2018', -&gt; MASTER_AUTO_POSITION=1 FOR CHANNEL "master2"; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql&gt; start slave; Query OK, 0 rows affected (0.01 sec) mysql&gt; show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.94 Master_User: cmzw Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 447 Relay_Log_File: server3-relay-bin-master1.000005 Relay_Log_Pos: 454 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.95 Master_User: cmzw Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: server3-relay-bin-master2.000002 Relay_Log_Pos: 367 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: </td> 
 </tr> 
 </tbody> 
</table>

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1JhaW5feGlhb2xvbmc_size_16_color_FFFFFF_t_70]: /images/20220415/5bd7770808cd4f299e74d4d4db4222eb.png