MySQL Database synchronization implementation

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.