MySQL 数据同步一主多从
Master 主服务器的ip:192.168.1.210
Slave1 从服务器的ip:192.168.1.216
Slave2 从服务器的ip:192.168.1.217
一、master主服务器上设置:
1.文件配置修改my.cnf配置文件
[mysqld]
加入下面两行,设置
log-bin = mysql-bin.log
server-id = 1
#binlog-do-db=juwang ?关于复制的数据库一定要分开写。不能写成binlog-do-db=juwang,test。这是无效的,两个数据库一个都不会复制。
#binlog-do-db=test
#binlog-ignore-db=mysql //避免同步mysql用户配置,以免不必要的麻烦(如果要选择数据库则在下面的两个slave都要做相应配置)
重启mysql服务。
#binlog-do-db=Industry //只同步Industry数据库
#binlog-ignore-db=mysql //避免同步mysql用户配置,以免不必要的麻烦
#binlog-ignore-db=performance_schema
#binlog-ignore-db=information_schema
2.权限设置 允许用户user从ip为192.168.1.216、192.168.1.217的主机连接到mysql服务器(master),并使用password作为密码
下面涉及到,从服务器的ip、登陆用户名、登陆密码。
下面用户名是xiaoxg,密码是123456
[root@mysql01 ~]# mysql -uxiaoxg -p123456
mysql> GRANT ALL PRIVILEGES ON *.* TO 'xiaoxg'@'192.168.1.216' IDENTIFIED BY '123456';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'xiaoxg'@'192.168.1.217' IDENTIFIED BY '123456';
mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
二、slave1从服务器上设置:
1.文件配置修改my.cnf配置文件
[mysqld]
加入下面两行,设置
log-bin = mysql-bin.log
server-id = 2
2.在从服务器上,关闭slave线程
Mysql>stop slave;
3.在从服务器做相应设置指定复制使用的用户,主数据库服务器的ip、端口、以及开始执行复制的日志文件和位置等。
mysql> change master to master_host='192.168.1.210', master_port=3306, master_user='xiaoxg', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=106;
4.在从服务器上,启动slave线程
Mysql >start slave;
mysql> show slave status\G;
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.210
Master_User: xiaoxg
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 6353
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 6353
Relay_Log_Space: 6799
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)
ERROR:
No query specified
三、slave2从服务器上设置:
1.文件配置修改my.cnf配置文件
[mysqld]
加入下面两行,设置
log-bin = mysql-bin.log
server-id = 3
2.在从服务器上,关闭slave线程
Mysql>stop slave;
3.在从服务器做相应设置指定复制使用的用户,主数据库服务器的ip、端口、以及开始执行复制的日志文件和位置等。
mysql> change master to master_host='192.168.1.210', master_port=3306, master_user='xiaoxg', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=106;
4.在从服务器上,启动slave线程
Mysql >start slave;
mysql> show slave status\G;
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.210
Master_User: xiaoxg
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 6353
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 6353
Relay_Log_Space: 6799
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)
ERROR:
No query specified
Master 主服务器的ip:192.168.1.210
Slave1 从服务器的ip:192.168.1.216
Slave2 从服务器的ip:192.168.1.217
一、master主服务器上设置:
1.文件配置修改my.cnf配置文件
[mysqld]
加入下面两行,设置
log-bin = mysql-bin.log
server-id = 1
#binlog-do-db=juwang ?关于复制的数据库一定要分开写。不能写成binlog-do-db=juwang,test。这是无效的,两个数据库一个都不会复制。
#binlog-do-db=test
#binlog-ignore-db=mysql //避免同步mysql用户配置,以免不必要的麻烦(如果要选择数据库则在下面的两个slave都要做相应配置)
重启mysql服务。
#binlog-do-db=Industry //只同步Industry数据库
#binlog-ignore-db=mysql //避免同步mysql用户配置,以免不必要的麻烦
#binlog-ignore-db=performance_schema
#binlog-ignore-db=information_schema
2.权限设置 允许用户user从ip为192.168.1.216、192.168.1.217的主机连接到mysql服务器(master),并使用password作为密码
下面涉及到,从服务器的ip、登陆用户名、登陆密码。
下面用户名是xiaoxg,密码是123456
[root@mysql01 ~]# mysql -uxiaoxg -p123456
mysql> GRANT ALL PRIVILEGES ON *.* TO 'xiaoxg'@'192.168.1.216' IDENTIFIED BY '123456';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'xiaoxg'@'192.168.1.217' IDENTIFIED BY '123456';
mysql> flush privileges;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
二、slave1从服务器上设置:
1.文件配置修改my.cnf配置文件
[mysqld]
加入下面两行,设置
log-bin = mysql-bin.log
server-id = 2
2.在从服务器上,关闭slave线程
Mysql>stop slave;
3.在从服务器做相应设置指定复制使用的用户,主数据库服务器的ip、端口、以及开始执行复制的日志文件和位置等。
mysql> change master to master_host='192.168.1.210', master_port=3306, master_user='xiaoxg', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=106;
4.在从服务器上,启动slave线程
Mysql >start slave;
mysql> show slave status\G;
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.210
Master_User: xiaoxg
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 6353
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 6353
Relay_Log_Space: 6799
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)
ERROR:
No query specified
三、slave2从服务器上设置:
1.文件配置修改my.cnf配置文件
[mysqld]
加入下面两行,设置
log-bin = mysql-bin.log
server-id = 3
2.在从服务器上,关闭slave线程
Mysql>stop slave;
3.在从服务器做相应设置指定复制使用的用户,主数据库服务器的ip、端口、以及开始执行复制的日志文件和位置等。
mysql> change master to master_host='192.168.1.210', master_port=3306, master_user='xiaoxg', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=106;
4.在从服务器上,启动slave线程
Mysql >start slave;
mysql> show slave status\G;
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.210
Master_User: xiaoxg
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 6353
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
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: 6353
Relay_Log_Space: 6799
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)
ERROR:
No query specified