Mysql Replication master-slave synchronization, mysqlreplication

Mysql Replication master-slave synchronization, mysqlreplication

Introduction:

Mysql master-slave synchronization is a common solution for enterprises. It is often used to back up databases. When the client operates the master database, the master database generates binlog files,
The slave database replicates the binlog log file of the master database and parses it into the corresponding SQL statement that is executed in the slave database to achieve master-slave consistency.
This solution only provides the log synchronization and execution function, while the slave database can only provide read operations. When the master server fails, you must manually handle the Failover, generally, a slave server is changed to the master server.

Master: 192.168.1.88
Slave: 192.168.1.80

1. Configure Master

[Root @ master ~] # Vim/etc/my. cnflog-bin = mysql-bin # enable binlog log server-id = 1 # Set the id number, which is globally unique [root @ master ~] # Service mysqld start [root @ master ~] # Netstat-anpt | grep 3306 # Check whether Mysql is successfully started [root @ master ~] # Chkconfig -- add mysqld [root @ master ~] # Chkconfig -- level 35 mysqld on [root @ master ~] # Iptables-I INPUT-s 192.168.1.80-p tcp -- dport 3306-j ACCEPT [root @ master ~] # Service iptables save

Ii. Configure Slave

[Root @ slave ~] # Vim/etc/my. cnfserver-id = 2 # Set the id number, this ID is globally unique relay-log = mysql-relay-bin # specify the relay-log File naming format replicate-wild-ignore-table = mysql. % # filter the table replicate-wild-ignore-table = test. % replicate-wild-ignore-table = information_schema. % replicate-wild-ignore-table = performance_schema. % [root @ slave ~] # Service mysqld start [root @ slave ~] # Chkconfig -- add mysqld [root @ slave ~] # Chkconfig -- level 35 mysqld on [root @ slave ~] # Iptables-I INPUT-s 192.168.1.88-p tcp -- dport 3306-j ACCEPT [root @ slave ~] # Service iptables save

3. Episode

4. Create a replication user and authorize the Master

[root@master ~]# mysql -u root -p123456mysql> grant replication slave on *.* to 'rep1_user'@'192.168.1.80' identified by 'rep1_passwd';mysql> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000008 | 324 | | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)

5. Add Master information to Slave

[root@slave ~]# mysql -u root -p123456mysql> change master to \-> master_host='192.168.1.88',-> master_user='rep1_user',-> master_password='rep1_passwd',-> master_log_file='mysql-bin.000008',-> master_log_pos=324;mysql> start slave;mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.88Master_User: rep1_userMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000008Read_Master_Log_Pos: 324Relay_Log_File: mysql-relay-bin.000002Relay_Log_Pos: 267Relay_Master_Log_File: mysql-bin.000008Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table: mysql.%,test.%,information_schema.%,performance_schema.%Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 324Relay_Log_Space: 430Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: b5e20203-86b5-11e4-b69c-000c29d099faMaster_Info_File: /usr/local/mysql/data/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:1 row in set (0.00 sec)

# Important: Slave_IO_Running and Slave_ SQL _Running master-slave replication threads. Under normal circumstances, both are YES!

# Note: Slave_IO_State, Master_Host, Master_Log_File, Read_Master_Log_Pos, Relay_Log_File, Relay_Log_Pos, Relay_Master_Log_File

# Replicate_Wild_Ignore_Table: You can see which databases (tables) are filtered during synchronization ).

Appendix:

3. Episode

# When data already exists in the master database before Master Slave synchronization, perform the following operations.

Mysql> flush tables with read lock; # The master database locks the table so that the database can only read and cannot write

# Keep this terminal and re-open a tty. If this terminal is disabled, the lock table becomes invalid.

[Root @ master ~] # Cd/usr/local/[root @ master local] # tar zcf mysql.tar.gz mysql # back up the entire database [root @ master local] # rsync-a mysql.tar.gz 192.168.1.80: /usr/local/# upload data to the slave database [root @ slave local] # service mysqld stop # disable Mysql [root @ slave local] # mv mysql old. mysql # rename the original mysql directory [root @ slave local] # tar zxf mysql.tar.gz # decompress the mysql directory of the master database [root @ master local] # service mysqld restart # restart the Mysql service of the master database [root @ slave local] # service mysqld start # start the slave database Mysql service