MySQL master-slave Synchronization

Check the default link encoding of MySQL on the master and slave servers, especially the slave server. Otherwise, garbled characters may occur during synchronization.

[Mysqld]

Server-id = 2
Slave-Skip-errors = all
Default-character-set = utf8
Expire_logs_days = 8;

[Client]
Default-character-set = utf8

 

BINLOG:

Set global expire_logs_days = 8;

Purge master logs to 'mysql-bin.010 ′;
Purge master logs before '2017-06-22 13:00:00 ′;
Clear BINLOG 3 days ago
Purge master logs before date_sub (now (), interval 3 day );
The date argument for the before variable can be in the 'yyyy-MM-DD hh: mm: ss' format. Both master and binary are synonyms.

 

 

 

Modify the master server:

Log-bin =/data/MySQL/bin

Binlog_do_db = UC
Binlog_do_db = web

# The following table does not require synchronization.

# BINLOG-ignore-DB = MySQL
# BINLOG-ignore-DB = test
# BINLOG-ignore-DB = information_schema
# BINLOG-ignore-DB = cacti

 

View the status of the primary database:

Show Master status;

Grant replication slave on *. * To 'sync' @ '%' identified by 'sync123456 ';

Change master to aster_host = '192. 168.145.222 ', master_user = 'tb', master_password = 'q123456', master_log_file = 'mysql-bin.000004', master_log_pos = 192;

Show slave status \ G

Slave_io_running: Yes // This status must be yes

Slave_ SQL _running: Yes // This status must be yes

 

If not Yes, run:

Stop slave; set global SQL _slave_skip_counter = 1000; Start slave;

Set GlobalSlave-Skip-Errors= 1062

 

MySQL BINLOG details

 

What is BINLOG
BINLOG logs are used to record all statements that have updated or potentially updated data (for example, a delete statement that does not match any row. The statement is saved as an "Event" and describes data changes.

BINLOG Function
Because BINLOG of data update is available, it can be used for real-time backup and replication with Master/Slave.

BINLOG-related parameters

Log_bin
Set this parameter to enable BINLOG and specify the path name.
Log_bin_index
Set this parameter to specify the path and name of the binary index file.
Binlog_do_db
This parameter indicates that only binary logs of the specified database are logged.
Binlog_ignore_db
This parameter indicates that the binary log of the specified database is not logged.
Max_binlog_cache_size
This parameter indicates the maximum memory size used by BINLOG.
Binlog_cache_size
This parameter indicates the memory size used by BINLOG. The state variables binlog_cache_use and binlog_cache_disk_use can be used for testing.
Binlog_cache_use: number of transactions cached using binary logs
Binlog_cache_disk_use: number of transactions in which binary logs are cached but the value of binlog_cache_size is exceeded and temporary files are used to save the statements in the transaction

Max_binlog_size
Maximum BINLOG value. The maximum value and default value are 1 GB. This setting does not strictly control the BINLOG size, especially when BINLOG is close to the maximum value and a large transaction is encountered, to ensure transaction integrity, you cannot switch logs. You can only record all SQL statements of the transaction into the current log until the transaction ends.

Sync_binlog
This parameter directly affects MySQL performance and integrity.

Sync_binlog = 0:
After the transaction is committed, MySQL only writes data in binlog_cache to the binlog file, but does not execute Disk Synchronization commands such as fsync to notify the file system to refresh the cache to the disk, the best performance is to let filesystem decide when to synchronize data.

Sync_binlog = n. After N transaction commits, MySQL will execute a Disk Synchronization command such as fsync, and the gay file system will refresh the BINLOG File Cache to the disk.

In MySQL, the default setting is sync_binlog = 0, that is, no mandatory disk refresh command is required. In this case, the performance is the best, but the risk is also the biggest. Once the system crashes, all BINLOG information in the file system cache will be lost.

Delete BINLOG
You can delete BINLOG manually or automatically.

Automatically delete BINLOG
Using the BINLOG parameter (expire_logs_days), MySQL automatically deletes BINLOG
Mysql> show binary logs;
Mysql> show variables like 'expire _ logs_days ';
Mysql> set global expire_logs_days = 3;

Manually delete BINLOG
Mysql> Reset master; // Delete the BINLOG of the master
Mysql> Reset slave; // Delete the slave relay log
Mysql> purge master logs before '2017-03-30 17:20:00 '; // Delete the BINLOG log file in the log index before the specified date
Mysql> purge master logs to 'binlog. 100'; // Delete the BINLOG log file in the log index of the specified Log File
Or you can directly use the operating system command to delete it.

Mysql> set SQL _log_bin = 1/0; // if you have the super permission, you can enable or disable the BINLOG Record of the current session.

Mysql> show Master logs; // view the BINLOG of the master
Mysql> show binary logs; // view the BINLOG log of the master node
Mysql> show Master Status; // provides the status information of the master binary log file.
Mysql> show slave hosts; // displays the list of currently registered slave instances. Slave that does not start with -- Report-host = slave_name is not displayed in this list

View binglog
You can use the mysqlbinlog command to view the BINLOG content.
[Root @ localhost ~] # Mysqlbinlog/home/MySQL/BINLOG/binlog.000003 | more

/*! 40019 set @ session. max_insert_delayed_threads = 0 */;
/*! 50003 set @ old_completion_type = @ completion_type, completion_type = 0 */;
Delimiter /*! */;
# At 4
#120330 16:51:46 server Id 1 end_log_pos 98 start: binlog v 4, server v 5.0.45-log created 120330 1
6:51:46
# Warning: This BINLOG was not closed properly. Most probably mysqld crashed writing it.
# At 196
#120330 17:54:15 server Id 1 end_log_pos 294 query thread_id = 3 exec_time = 2 error_code = 0
Set timestamp = 1333101255 /*! */;
Insert into TT7 select * From TT7 /*! */;
# At 294
#120330 17:54:46 server Id 1 end_log_pos 388 query thread_id = 3 exec_time = 28 error_code = 0
Set timestamp = 1333101286 /*! */;
Alter table TT7 engine = InnoDB /*! */;

 

Parse BINLOG format

Location
The location in the file. "at 196" indicates the start point of the "event", starting from 196th bytes. "end_log_pos 294" indicates that it ends in 294th bytes.

 

Timestamp
Time stamp of the event: "120330 17:54:46"

 

Event execution time
Time spent on event execution: "exec_time = 28"

 

Error Code
Error Code: "error_code = 0"

 

Server ID
Server ID: "server Id 1"