MySQL Database synchronization implementation

1. Set Master Server

Server-id=1
Log-bin=/var/lib/mysql/mysql-bin

In order to use transactional InnoDB for maximum persistence and consistency in replication, you should specify the innodb_flush_log_at_trx_commit=1,sync_binlog=1 option
Innodb_flush_log_at_trx_commit=1
Sync_binlog=1

2. set Slave Server

Binlog_format=mixed

max_allowed_packet=12m

Lower_case_table_names=1

Server-id = 8

Report-host=xxx.xxx.xxx.xxx

Report-user=replslave

Report-password=xxx

Log-bin=slave-bin.log

3. User Information Synchronization

The user name password for creating the view is logged when the user creates the view, and so on. So you need to create the same username and host address

1. A. if it is possible to lock the master server's data

A) lock the master server

Flush tables with read lock;

b) Query Binlog status

Show master status;

c) Export Master data

mysqldump--default-character-set=utf8mb4--opt-u admin--password=iloveqihuohui!-h Master address backup Database > Bak.sql

Re-open Host Master write operation function

Unlock tables;

d) Import master data to Slave server

Mysql-uroot-p--DEFAULT-CHARACTER-SET=UTF8MB4 dbname <bak.sql

e) Setting up the slave server

Stop slave;

Mysql>change Master to master_host= ' 192.168.1.2 ',
->master_user= ' Repl_user ',
->master_password= ' 123456 ',
->master_log_file= ' mysql-bin.000013 ',
->master_log_pos=7863951;

Start slave;

1. B. if the master server's data cannot be locked

A) query Binlog status

Show master status;

b) Export Master data

mysqldump--default-character-set=utf8mb4--opt-u admin--password=iloveqihuohui!-h Master address backup Database > Bak.sql

c) Import the master data to the slave server

Mysql-uroot-p--DEFAULT-CHARACTER-SET=UTF8MB4 dbname <bak.sql

d) Confirm Binlog specific location

Need to find a nearby location and navigate to the specific SQL statement

Show Binlog Events in ' mysql-bin.000013 ' from 7863951

e) Setting up the slave server

Stop slave;

Mysql>change Master to master_host= ' 192.168.1.2 ',
->master_user= ' Repl_user ',
->master_password= ' 123456 ',
->master_log_file= ' mysql-bin.000013 ',
->master_log_pos=7863951;

Start slave;

Automatic backup script:

You need to create a self-read user who needs the following permissions for the database to be backed up:

Select, Lock Tables, Show View

The script is as follows:

@echo off

Set h=%time:~0,2%

Set H=%h: =0%

Set "ymd=%date:~,4%%date:~5,2%%date:~8,2%%h%"

MD g:\db_backups\%Ymd%

"D:\xampp\mysql\bin\mysqldump"--default-character-set=utf8mb4--opt-u dev--password=winbei888-h 10.100.1.76 Winbei _production >g:\db_backups\%ymd%\winbeidb.sql

"D:\xampp\mysql\bin\mysqldump"--default-character-set=utf8mb4--opt-u dev--password=winbei888-h 10.100.1.76 OpenFire >g:\db_backups\%ymd%\openfiredb.sql

G:\db_backups\7za.exe A-rg:\db_backups\backdata\%ymd%.zip g:\db_backups\%ymd%\*

Rd g:\db_backups\%Ymd%\/s/q

@echoon

MySQL Database synchronization implementation