MySQL进阶运维与架构添加从库

31.3　添加MySQL从库

MySQL支持在原有主从复制环境中，再次添加MySQL从库到主从复制环境中。本节就在31.1节的基础上添加一个MySQL从库到主从复制环境中，从而搭建MySQL的一主两从复制环境。

31.3.1　服务器规划

添加MySQL从库到原有主从环境后的服务器规划如表31-3所示。

表31-3　添加MySQL从库后的服务器规划

MySQL的一主两从数据库复制示意图如图31-3所示。

图31-3　MySQL一主两从数据库复制示意图

31.3.2　在主从服务器上进行的操作

1．在主服务器（binghe151服务器）上进行的操作

（1）在MySQL命令行创建MySQL数据库主从复制的账户信息。

mysql> CREATE USER 'binghe153'@'192.168.175.153' IDENTIFIED BY 'binghe153';   
Query OK, 0 rows affected (0.11 sec)
mysql> ALTER USER 'binghe153'@'192.168.175.153' IDENTIFIED WITH mysql_native_password BY 'binghe153';  
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'binghe153'@'192.168.175.153'; 
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

（2）在主数据库上执行锁表操作，禁止再向主数据库中插入数据以获取主数据库的二进制日志坐标。

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

此时，主数据库上的所有表都会添加读锁，不会再向数据表中写入数据。

（3）重新打开命令行终端，连接binghe151服务器，登录MySQL并查看主数据库的二进制日志文件和日志文件的位置信息。

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 |     5960 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

二进制日志文件为mysql-bin.000007，坐标位置为5960。

（4）导出MySQL中的数据。

[root@binghe151 ~]# mysqldump -uroot -p -hlocalhost -P3306 --all-databases --triggers --routines 
--events > /backup/mysql/all_databases_binghe153.sql 
Enter password:

（5）在步骤（2）中的MySQL命令行解锁MySQL中的数据表。

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

（6）将导出的数据文件复制到binghe153服务器上。

[root@binghe151 ~]# scp /backup/mysql/all_databases_binghe153.sql 192.168.175.153:/backup/mysql/
root@192.168.175.153's password: 
all_databases_binghe153.sql          100% 1000KB 999.8KB/s   00:00

2．在从服务器（binghe153服务器）上执行的操作

（1）配置my.cnf文件，添加的配置项与主服务器上的my.cnf文件基本相同，只是server_id选项不同。

vim /data/mysql/conf/my.cnf
server_id = 153

（2）重启MySQL服务。

[root@binghe153 ~]# service mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL.... SUCCESS!

（3）将从主服务器上复制的数据文件导入MySQL数据库中。

[root@binghe153 ~]# mysql -uroot -p -hlocalhost -P3306 < /backup/mysql/all_databases_binghe153.sql 
Enter password:

（4）登录MySQL，设置主从复制的信息，包括主服务器的主机名（IP地址）、端口，用于数据复制的用户名、密码，以及当前主数据库的二进制日志文件和当前二进制日志文件的位置等信息。

mysql> CHANGE MASTER TO MASTER_HOST='192.168.175.151',
    -> MASTER_PORT=3306,
    -> MASTER_USER='binghe153',
    -> MASTER_PASSWORD='binghe153',
    -> MASTER_LOG_FILE='mysql-bin.000007',
    -> MASTER_LOG_POS=5960;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

（5）启动从数据库的复制线程。

mysql> START slave;
Query OK, 0 rows affected (0.00 sec)

（6）查看从数据库的运行状态。

mysql> SHOW slave STATUS \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.175.151
                  Master_User: binghe153
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 5960
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

binghe153服务器上的从数据库运行正常。

31.3.3　测试MySQL主从复制环境

（1）在binghe151服务器上的MySQL主数据库中，向testdb数据库下的t_user数据表中插入一条测试数据。

mysql> INSERT INTO testdb.t_user
    -> (id, t_name)
    -> VALUES
    -> (4, 'binghe004');
Query OK, 1 row affected (0.00 sec)

（2）在binghe152服务器上的MySQL从数据库中查看数据。

mysql> SELECT * FROM testdb.t_user;
+----+-----------+
| id | t_name    |
+----+-----------+
|  1 | binghe    |
|  2 | binghe002 |
|  3 | binghe003 |
|  4 | binghe004 |
+----+-----------+
4 rows in set (0.00 sec)

在主数据库上添加的姓名为binghe004的数据，已经成功同步到binghe152服务器上的MySQL中。

（3）在binghe153服务器上的MySQL从数据库中查看数据。

mysql> SELECT * FROM testdb.t_user;
+----+-----------+
| id | t_name    |
+----+-----------+
|  1 | binghe    |
|  2 | binghe002 |
|  3 | binghe003 |
|  4 | binghe004 |
+----+-----------+
4 rows in set (0.00 sec)

可以看到，数据同样成功同步到了binghe153服务器上的MySQL中，由此说明MySQL从库添加成功。

注意：MySQL同样支持向主从环境中添加主库，此时有两种情况。

·添加主库后只有一个主库。将新添加的MySQL数据库设置为原有主数据库的主库，将原有的所有从数据库指向新添加的MySQL数据库。此时，主数据库为新添加的MySQL数据库，其他MySQL数据库都为从数据库。

·添加主库后有两个主库。将新添加的MySQL数据库设置为原有主数据库的主库和从库，其他配置不变。此时，主数据库为原有的MySQL主数据库和新添加的MySQL数据库，其他数据库为从数据库。

针对以上两种情况，读者可自行实现，这里不再赘述。