搭建主从复制
#master
[client]
port = 3306
socket = /tmp/mysql.sock
# The MySQL server
[mysqld]
# Basic
port = 3306
user = mysql
server-id = 1
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3306/data
tmpdir = /data/mysql/mysql3306/tmp
socket = /tmp/mysql.sock
log-bin = /data/mysql/mysql3306/logs/mysql3306-bin
log-bin-index=mysql3306-bin.index
log-error = error.log
slow-query-log-file = slow.log
skip-external-locking
skip-name-resolve
log-slave-updates
lower_case_table_names = 1 #忽略表名大小写
character_set_server = gbk
innodb_file_per_table = 1
innodb_autoinc_lock_mode = 2
explicit_defaults_for_timestamp = true
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#slave1
mysql> system cat /etc/my.cnf
[client]
port = 3307
socket = /tmp/mysql.sock
# The MySQL server
[mysqld]
# Basic
port = 3307
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3307/data
tmpdir = /data/mysql/mysql3307/tmp
socket = /tmp/mysql.sock
relay-log-index = slave-relay-bin3307.index
relay-log = slave-relay-bin3307
server-id = 2
log-error = error.log
slow-query-log-file = slow.log
skip-external-locking
skip-name-resolve
log-slave-updates
lower_case_table_names = 1 #忽略表名大小写
character_set_server = gbk
innodb_file_per_table = 1
innodb_autoinc_lock_mode = 2
explicit_defaults_for_timestamp = true
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#slave2
[client]
port = 3308
socket = /tmp/mysql.sock
# The MySQL server
[mysqld]
# Basic
port = 3308
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3308/data
tmpdir = /data/mysql/mysql3308/tmp
socket = /tmp/mysql.sock
log-bin = /data/mysql/mysql3308/logs/mysql3308-bin
relay-log-index = slave-relay-bin3308.index
relay-log = slave-relay-bin3308
server-id = 3
slow-query-log-file = slow.log
skip-external-locking
skip-name-resolv
log-slave-updates
lower_case_table_names = 1 #忽略表名大小写
character_set_server = gbk
innodb_file_per_table = 1
innodb_autoinc_lock_mode = 2
explicit_defaults_for_timestamp = true
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#master
mysql> create user repl_user;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to repl_user identified by '111111';
Query OK, 0 rows affected (0.00 sec)
mysql> mysql> show master status\G
*************************** 1. row ***************************
File: mysql3306-bin.000001
Position: 415
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
#slave1
mysql> change master to master_host='linux-node1.example.com',master_port=3306,master_user='repl_user',master_password='111111';
Query OK, 0 rows affected, 2 warnings (0.01 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: linux-node1.example.com
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306-bin.000001
Read_Master_Log_Pos: 415
Relay_Log_File: slave-relay-bin3307.000002
Relay_Log_Pos: 582
Relay_Master_Log_File: mysql3306-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: 415
Relay_Log_Space: 759
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
Master_UUID: 496be755-657b-11e8-b344-000c29cc5233
Master_Info_File: /data/mysql/mysql3307/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
#slave2
mysql> change master to master_host='linux-node1.example.com',master_port=3306,master_user='repl_user',master_password='111111';
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql>
mysql>
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: linux-node1.example.com
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306-bin.000001
Read_Master_Log_Pos: 415
Relay_Log_File: slave-relay-bin3308.000002
Relay_Log_Pos: 582
Relay_Master_Log_File: mysql3306-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: 415
Relay_Log_Space: 759
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
Master_UUID: 496be755-657b-11e8-b344-000c29cc5233
Master_Info_File: /data/mysql/mysql3308/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.01 sec)
#master创建用户并授权
mysql> create user mats;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave,reload,create user,super on *.* to mats@'192.168.158.%' with grant option;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
#创建表并刷新日志
mysql> create table tbl(text TEXT);
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into tbl values("Yeah! Replication!");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
mysql>
mysql> select * from tbl;
+--------------------+
| text |
+--------------------+
| Yeah! Replication! |
+--------------------+
1 row in set (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show binlog events\G
*************************** 1. row ***************************
Log_name: mysql3306-bin.000001
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 120
Info: Server ver: 5.6.24-log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql3306-bin.000001
Pos: 120
Event_type: Query
Server_id: 1
End_log_pos: 212
Info: create user repl_user
*************************** 3. row ***************************
Log_name: mysql3306-bin.000001
Pos: 212
Event_type: Query
Server_id: 1
End_log_pos: 415
Info: GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA'
*************************** 4. row ***************************
Log_name: mysql3306-bin.000001
Pos: 415
Event_type: Rotate
Server_id: 1
End_log_pos: 466
Info: mysql3306-bin.000002;pos=4
4 rows in set (0.00 sec)
mysql> show master status\G
*************************** 1. row ***************************
File: mysql3306-bin.000004
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
#slave1
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: linux-node1.example.com
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306-bin.000004
Read_Master_Log_Pos: 120
Relay_Log_File: slave-relay-bin3307.000008
Relay_Log_Pos: 287
Relay_Master_Log_File: mysql3306-bin.000004
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: 120
Relay_Log_Space: 515
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
Master_UUID: 496be755-657b-11e8-b344-000c29cc5233
Master_Info_File: /data/mysql/mysql3307/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
#slave2
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: linux-node1.example.com
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306-bin.000004
Read_Master_Log_Pos: 120
Relay_Log_File: slave-relay-bin3308.000008
Relay_Log_Pos: 287
Relay_Master_Log_File: mysql3306-bin.000004
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: 120
Relay_Log_Space: 515
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
Master_UUID: 496be755-657b-11e8-b344-000c29cc5233
Master_Info_File: /data/mysql/mysql3308/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
-----------------------END----------------------------
