MySQL 主从服务器的配置步骤 Friday, November 22, 2013 2:05 PM

MySQL 主从服务器的配置步骤

环境：

[plain]

view plain copy print ? Master ：

ip 192.168.1.110 os rhel-5.8 mysql 5.5.28 Slave ：

ip 192.168.1.113 os rhel-5.8 mysql 5.5.28

①

主 my.cnf 的配置 [plain]

view plain copy print ? [root@localhost ~]# cat /etc/my.cnf [mysqld] log-bin=mysql-bin # 打开 mysql 二进制日志

binlog-do-db=test # 设置二进制日志记录的库

server-id = 1 # 设置 mysql_id ，主从不能相同

binlog-ignore-db=mysql # 设置二进制日志不记录的库

sync_binlog=1 # 设置 binlog 有更新的时候刷新到磁盘

② 登入主库 [plain]

view plain copy print ? use mysql; grant replication slave,file on . to ‘Think’@’192.168.1.113’ identified by ‘mysql’; flush privileges;

查看主库状态，并记录 File 和 Position [plain]

view plain copy print ? mysql> show master status\G; ********* 1. row ************* File: mysql-bin.000002 Position: 107 Binlog_Do_DB: test Binlog_Ignore_DB: mysql 1 row in set (0.00 sec)

③ 从my.cnf的配置 [plain]

view plain copy print ? [root@localhost ~]# cat /etc/my.cnf [mysqld] log-bin=mysql-bin server-id = 2 replicate-do-db=test replicate-ignore-db=mysql log-slave-updates sync_binlog=1 slave-net-timeout=10

④ 从库change master [html]

view plain copy print ? mysql

stop slave; Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql

change master to

master_host

‘192.168.1.110’ , master_user = ‘Think’ , master_password = ‘mysql’ , -

master_log_file

‘mysql-bin.000002’ , master_log_pos = 107 ; Query OK, 0 rows affected (0.10 sec)

mysql

start slave; Query OK, 0 rows affected (0.00 sec)

mysql

show slave status\G; ********* 1. row ************* Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.110 Master_User: Think Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 107 Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000002 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: 107 Relay_Log_Space: 413 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: 1 1 row in set (0.00 sec)

⑤

验证同步

主库： [plain]

view plain copy print ? mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql> show tables; +—————-+ | Tables_in_test | +—————-+ | bin | | servers | | water | +—————-+ 3 rows in set (0.00 sec)

mysql> insert into bin values(1); Query OK, 1 row affected (0.12 sec)

从库： [plain]

view plain copy print ? mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql> show tables; +—————-+ | Tables_in_test | +—————-+ | bin | | sales | | t | +—————-+ 3 rows in set (0.00 sec)

mysql> select * from bin; +——+ | id | +——+ | 1 | +——+ 1 row in set (0.00 sec)

已使用 Microsoft OneNote 2016 创建。