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

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-logread-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

4、得到主服务器当前二进制日志名和偏移量，这个操作的目的是为了在从数据库启动后，从这个点开始进行数据的恢复。
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 | 517 | test | mysql |
+------------------+----------+--------------+------------------+
1 row in set （0.00 sec）

最好在主数据库备份完毕，恢复写操作。
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 = 2log_bin = /var/log/mysql/mysql-bin.logmaster-host =192.168.10.111master-user =backupmaster-pass =123456master-port =3306master-connect-retry=60 #如果从服务器发现主服务器断掉，重新连接的时间差（秒）replicate-do-db =test #只复制某个库replicate-ignore-db=mysql #不复制某个库7、在从服务器上,启动slave进程mysql> start slave;
8、在从服务器进行show salve status验证mysql> SHOW SLAVE STATUSG*************************** 1. row *************************** Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.10.111Master_User: rootMaster_Port: 3306Connect_Retry: 5Master_Log_File: mysql-bin.000007Read_Master_Log_Pos: 263 Relay_Log_File: mysqld-relay-bin.000002Relay_Log_Pos: 408Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: testReplicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 263Relay_Log_Space: 564Until_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: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_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 -p123456mysql> 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: 2Duplicates: 0Warnings: 0mysql> 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: 1Changed: 1Warnings: 0mysql> 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: 5Duplicates: 0Warnings: 0mysql> 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 userMODIFY COLUMN name VARCHAR（200）;Query OK, 5 rows affected （0.01 sec）Records: 5Duplicates: 0Warnings: 0mysql> 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 changedmysql> 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 eventsG;*************************** 12. row *************************** Log_name: mysql-bin.000007Pos: 985 Event_type: QueryServer_id: 1End_log_pos: 1075 Info: use `test`; delete from user where id=2*************************** 13. row *************************** Log_name: mysql-bin.000007Pos: 1075 Event_type: QueryServer_id: 1End_log_pos: 1175 Info: use `test`; alter table user add name varchar（50）*************************** 14. row *************************** Log_name: mysql-bin.000007Pos: 1175 Event_type: QueryServer_id: 1End_log_pos: 1287 Info: use `test`; ALTER TABLE userMODIFY COLUMN name VARCHAR（200）*************************** 15. row *************************** Log_name: mysql-bin.000007Pos: 1287 Event_type: QueryServer_id: 1End_log_pos: 1376 Info: use `test`; create table user2（id int）15 rows in set （0.00 sec）本文永久更新链接地址