MySQL Master-Slave synchronization

Before we talk about MySQL master-slave synchronization, we first understand the normal file data synchronization.

Data synchronization for normal files

1.NFS Network file sharing can store data synchronously

2.samba Shared--windows Platform

3. Timed tasks or daemons combined with RSYNC,SCP

4.inotify+rsync Real-time synchronization

5.FTP Data Synchronization

6.svn

......

mysql sync

MySQL has its own sync feature, and MySQL synchronization is not a direct sync of files on disk.

MySQL supports one-way, bidirectional, chained cascading, fact, asynchronous replication. During the replication process, one server acts as the primary server master, while one or more other servers act as slave from the server.

Replication can be one-way: M==>s, can also be bidirectional; M<==>m, of course, can also be more from multi-M loop synchronization and so on.

If chained cascade replication is set, the server itself, in addition to acting from the server itself, also acts as the primary server from the server under it.

Chained cascade replication: a-->b-->c-->d Copy Form

In the current production work, most of the MySQL master-slave synchronization is asynchronous replication method, that is, not strictly real-time data synchronization.

Production environment Licensing

Main Library User: Grant SELECT, INSERT, Update,delete on ' xx '. * to ' user ' @ ' network segment ' identified by ' user password ';

From library: Grant Select on ' xx '. * to ' user ' @ ' network segment ' identified by ' user password ';

Can be combined with read-only parameters to do together

The simplest way to implement the above authorization scheme is to configure the Binlog-ignore-db=mysql in the main library.

When the master-slave synchronization is configured, all database content updates must be made on the primary server, preventing the user from updating the database contents of the primary server against inconsistencies in data updates from the server, resulting in conflicts

How do I ensure that users update on the master server?

1. Prevent writing data from the library method

Take a synchronization that ignores the authorization table, and then grant only the Select Read permission to the user from the server. MySQL Library with different steps

2. Prevent writing data from the library method

In addition to select authorization, adding parameters to the Slave server startup option or adding read-only to the MY.CNF configuration file guarantees read-only from the library, both of which result in better operation

Application Scenarios

1. master-slave servers are backed up by each other

2. master-Slave server read-write separation share site pressure

3. Independent sharing of pressure based on server split business

In an enterprise production environment, a read-write separation strategy is usually taken, that is, the main library is responsible for write operations, and the library is responsible for read operations. Generally from the library has more than one, the main library can use high-availability means to achieve automatic failover, such as the use of HEARTBEAT+DRBD. If the Web site reads more stressful, it can also take advantage of load balancing to share the pressure read from the library server.

MySQL Read-write separation method

1. Read/write separation through the program (performance, efficiency is best), such as Php,java program

2. Through the software implementation, such as Mysql-proxy amoeba and other agent software can also achieve read and write separation, but the most commonly used best is the program to achieve read and write separation.

MySQL master-slave replication principle

MySQL master-slave replication is an asynchronous replication process (in general it feels like real-time synchronization) and data is copied from master to slave. This process is done by three threads, two threads (SQL thread and IO thread) on the slave side, and another thread (IO thread) on the master side. The IO thread on the slave side is responsible for dealing with threads on the master side

To achieve MySQL master-slave replication, you must first turn on the master side of the Binlog (mysql-bin.xxx) function, or you can not achieve master-slave replication. Because the entire replication process is actually slave get the Binlog log from the master side, and then perform the operations that are recorded in the Binlog log in the same order on slave itself.

When the user writes the data to the main library, the main library puts these SQL statements (the database's change statements) into the Binlog, deals with the IO thread and the main library IO thread after the library is opened, provides the user name password, log file and location information to the main library, The main library is validated by reading the Binlog information (based on the requirements from the library IO County thread) back to the From library IO, the location information is saved in Master-info, the SQL is placed in the Relay-log (trunk log), the SQL thread is executed sequentially from the library, the library IO thread continues to read Master-info information, and then interacts with the main library IO.

mysql master-slave replication Practice

1. Defining server Roles

Main Library (MySQL master):IP 192.168.132.10 port:3306

from library (MySQL slave): IP 192.168.132.20 port: 3306

2. Database Environment Preparation

The practice environment takes two virtual machines as the practice object and carries out one-way master-slave replication.

Virtual machines are installed with MySQL 5.1.72 version and the database is started

3. performing actions on the main library

set the Server-id value and turn on the binlog parameter

Vim/etc/my.cnf

Open Log-bin

[Mysqld]

Server-id = 1

# Uncomment the following if you want to log updates

Log-bin=mysql-bin

because the virtual machine mysql installation path is /usr/local/mysql, the data path is /usr/local/mysql/data, which is the log file under that path.

Server-id can't be the same

Tips:

1. Server-id with the Log-bin must be placed in mysqld in the module

2. Server-id the value of using the server IP the last of the address 8 bit as Ten to avoid duplication of different machines or instances (not suitable for multiple instances)

3. now the configuration file to find the relevant parameters, do not exist when added, parameters do not repeat

4. A database restart is required to modify the configuration file

Check if it takes effect :

[Email protected] data]# mysql-uroot-p ' 123456 '-e ' show variables like ' Log_bin ';

+---------------+-------+

| variable_name | Value |

+---------------+-------+

| Log_bin | On |

4.create an account to copy from the library

Log in to the main library MySQL

Grant Replication Slave on * * to [e-mail protected] ' 192.168.132.% ' identified by ' rep99 ';

Flush Permissions flush privileges;

Check User

Select User,host from Mysql.user;

+------+---------------+

| user | Host |

+------+---------------+

| Root | %             |

| Root | 127.0.0.1 |

| Rep | 192.168.132.% |

| Root | localhost |

+------+---------------+

4 rows in Set (0.00 sec)

5.read-only to the Database lock table (do not turn off the current window)

Because you want to back up the database, the lock table guarantees that the data will be exported consistently

Flush tables with read lock;

The current state, that is , the current binlog log file name and binary binlog log offset, which are required for subsequent synchronization from the library.

Show master status;

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

|     mysql-bin.000001 |              1336 |                  | |

+------------------+----------+--------------+------------------+

1 row in Set (0.00 sec)

6.Export Database backup

Open a new window, export the database data, if the data is large (100g+), and allow downtime, you can pull the library directly packaged data file migration

Mysqldump-uroot-p ' 123456 '-a-b|gzip>/home/tuwei/new.sql.gz

-a means to back up all libraries -B to increase the use DB and drop , etc. (the original is overwritten directly by the Guide library)

7.  unlock the main library

Unlock the main library, restore writable

mysql> unlock table;

Query OK, 0 rows Affected (0.00 sec)

8. To perform an action from a library

set the Server-id value and close the Binlog parameter

This will be set from the library Server-id value to 2,Log-bin commented out, note to be in the mysqld module

There are two situations in which you need to open Binlog:

1. Cascade sync a->b->c The middle of the B will open

2. make database backup from library, database backup needs to be fully prepared and Binlog

Restart the database after Setup is complete

9.  Import Database to from library

The database files backed up on the main library are transferred to the import from the library and can be delivered using the SCP, SFTP, and other commands.

Unzip the database file

Gzip-d new.sql.gz

Import Database

Mysql-uroot-p ' 123456 ' <new.sql

10.   To Configure synchronization parameters from a library

you can quickly execute the change Master statement (for scripting) without logging into the database.

Cat |mysql-uroot-p ' 123456 ' <<eof

> Change MASTER to

> master_host= ' 192.168.132.10 ',

> master_port=3306,

> master_user= ' rep ',

> master_password= ' rep99 ',

> master_log_file= ' mysql-bin.000001 ',

> master_log_pos=1336;

> EOF

11. To start the sync switch from the library

Slave start;

Then view the status from the library

show slave status\g;

See the key three points

Slave_io_running:yes

Slave_sql_running:yes

seconds_behind_master:0

Indicates that synchronization is complete

MySQL Master-Slave synchronization