CentOS搭建MySQL主从复制，读写分离

138次阅读

共计 6313 个字符，预计需要花费 16 分钟才能阅读完成。

MySQL 主从复制的优点:
1、如果主服务器出现问题，可以快速切换到从服务器提供的服务，保证高可用性
2、可以在从服务器上执行查询操作，降低主服务器的访问压力
3、可以在从服务器上执行备份，以避免备份期间影响主服务器的服务

注意事项：
1、server-id 必须唯一，一般使用 ip 的后三位
2、从库 Slave_IO_Running:NO 可能原因: 帐号无权限操作
3、Can’t execute the query because you have a conflicting read lock，解锁下即可 unlock tables;
4、一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从服务器查询，实时性要求高的数据仍然需要从主数据库获得
5、修改完主从服务器的配置需要重启 mysql：service mysqld restart

主机 A: 192.168.10.111
从机 B: 192.168.10.124

请先分别安装 mysql，版本需一致，装了即可跳过

yum install mysql mysql-server #输入 y 即可自动安装, 直到安装完成

1、先登录主机 A，在主服务器上，设置一个从数据库的账户，使用 REPLICATION SLAVE（从复制）赋予权限，如：
mysql>GRANT REPLICATION SLAVE ON *.* TO ‘backup’@’192.168.10.124’ IDENTIFIED BY ‘123456’
赋予从机权限，有多台从机，就执行多次。
mysql>flush privileges;

2、打开主机 A 的 my.cnf，输入如下：（修改主数据库的配置文件 my.cnf，开启 BINLOG，并设置 server-id 的值，修改之后必须重启 mysql 服务）

server-id               = 1    #主机标示，整数
log_bin                 = /var/log/mysql/mysql-bin.log   #确保此文件可写，开启 bin-log
read-only              =0  #主机，读写都可以
binlog-do-db         =test   #需要备份数据，多个写多行
binlog-ignore-db    =mysql #不需要备份的数据库，多个写多行
可以通过 mysql>show variables like 'log_%'; 验证二进制日志是否已经启动。

3、现在可以停止主数据的的更新操作，并生成主数据库的备份，我们可以通过 mysqldump 到处数据到从数据库，当然了，你也可以直接用 cp 命令将数据文件复制到从数据库去，注意在导出数据之前先对主数据库进行 READ LOCK，以保证数据的一致性
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.19 sec)

然后 mysqldump 导出数据：
mysqldump -h127.0.0.1 -p3306 -uroot -p test > /data/backup/test.sql

最好在主数据库备份完毕，恢复写操作。
mysql> unlock tables;
Query OK, 0 rows affected (0.28 sec)

5、将刚才主数据备份的 test.sql 复制到从数据库（navicat、phpmyadmin、命令行都可以），进行导入。

6、修改从数据库的 my.cnf，增加 server-id 参数，指定复制使用的用户，主数据库服务器的 ip，端口以及开始执行复制日志的文件和位置。打开从机 B 的 my.cnf，输入（修改之后必须重启 mysql 服务）

server-id       = 2
log_bin         = /var/log/mysql/mysql-bin.log
master-host     =192.168.10.111
master-user     =backup
master-pass     =123456
master-port     =3306
master-connect-retry=60 #如果从服务器发现主服务器断掉，重新连接的时间差(秒)
replicate-do-db =test #只复制某个库
replicate-ignore-db=mysql #不复制某个库

7、在从服务器上, 启动 slave 进程

mysql> start slave;

8、在从服务器进行 show salve status 验证

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.111
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 5
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 263
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 408
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: mysql
           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: 263
              Relay_Log_Space: 564
              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:
1 row in set (0.00 sec)

提示
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
说明配置成功了

9、测试主从服务器是否能同步
插入修改删除增加字段修改字段增加表自己测试都可以

[linuxidc@server22 ~]$mysql -uroot -p123456
mysql> create database test;
mysql> create table user(id int);
mysql> insert into user values(1),(2),(3),(4),(5),(6);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2rows in set (0.00 sec)


mysql> select * from user;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)


mysql> update user set id=11 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from user;
+----+
| id |
+----+
| 11 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)


mysql> delete from user where id=2;
Query OK, 1 row affected (0.00 sec)


mysql> select * from user;
+----+
| id |
+----+
| 11 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
5 rows in set (0.00 sec)


mysql> alter table user add name varchar(50);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0


mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 11 | NULL |
|  3 | NULL |
|  4 | NULL |
|  5 | NULL |
|  6 | NULL |
+----+------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE user  MODIFY COLUMN name VARCHAR(200);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0


mysql> desc user;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create table user2(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
| user           |
| user2          |
+----------------+
3 rows in set (0.00 sec)
mysql>

在从服务器查看是否同步过来如果一致说明成功

mysql> use test;
Database changed
mysql> select * from user;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
2 rows in set (0.00 sec)

mysql> select * from user;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)

mysql> select * from user;
+----+
| id |
+----+
| 11 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)

mysql> select * from user;
+----+
| id |
+----+
| 11 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
5 rows in set (0.00 sec)

mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 11 | NULL |
|  3 | NULL |
|  4 | NULL |
|  5 | NULL |
|  6 | NULL |
+----+------+
5 rows in set (0.00 sec)
mysql> desc user;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(200) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
| user           |
| user2          |
+----------------+
3 rows in set (0.00 sec)

mysql>

mysql binlog 日志查看：show binlog events\G;

*************************** 12. row ***************************
   Log_name: mysql-bin.000007
        Pos: 985
 Event_type: Query
  Server_id: 1
End_log_pos: 1075
       Info: use `test`; delete from user where id=2
*************************** 13. row ***************************
   Log_name: mysql-bin.000007
        Pos: 1075
 Event_type: Query
  Server_id: 1
End_log_pos: 1175
       Info: use `test`; alter table user add name varchar(50)
*************************** 14. row ***************************
   Log_name: mysql-bin.000007
        Pos: 1175
 Event_type: Query
  Server_id: 1
End_log_pos: 1287
       Info: use `test`; ALTER TABLE user  MODIFY COLUMN name VARCHAR(200)
*************************** 15. row ***************************
   Log_name: mysql-bin.000007
        Pos: 1287
 Event_type: Query
  Server_id: 1
End_log_pos: 1376
       Info: use `test`; create table user2(id int)
15 rows in set (0.00 sec)

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-09/135121.htm

正文完

星哥说事-微信公众号