CentOS服务器平台搭建mysql主从复制与读写分离的方法

最好在主数据库备份完毕，恢复写操作。

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 STATUSG 
*************************** 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、测试主从服务器是否能同步

插入 修改 删除 增加字段 修改字段 增加表自己测试都可以

[xjp@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> 

								 
 2/3   首页 上一页 1 2 3 下一页 尾页