MySQL Master-slave replication

1.1 MySQLIntroduction to Master-slave replication

The master-slave replication scheme for MySQL databases, similar to file-level replication using commands such as Scp/rsync, is the remote transmission of data, except that MySQL 's master-slave replication is its own feature, without the use of third-party tools, and The master-slave copy of MySQL is not a direct copy of the file on the database disk, but is copied to the server to be synchronized by the logical Binlog log, which is then re-applied to the SQL statement in the local thread-read log. MySQL database.

MySQL Database supports the replication of one-way, two-way, chained cascade, ring-like, and different business scenarios. During the replication process, one server acts as the primary server (Master), receives content updates from users, and one or more other servers act as slave servers (Slave), receiving Binlog from the primary server. The log contents of the file, parsing the SQL re-update to the slave server, so that the master-slave server to achieve consistency.

if chained-level replication is set,the slave (slave) server itself, in addition to acting as a slave server, also acts as the primary server from the server under it. Chained cascade replication is similar to the A-->b-->c replication form.

650) this.width=650; "Width=" 544 "height=" 382 "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

1.2 MySQLEnterprise Application Scenarios for master-slave replication

MySQL's master-slave replication clustering feature enables MySQL databases to support large-scale, high-concurrency reads and writes, while effectively protecting data backups of physical server downtime scenarios.

Application Scenario One: real-time data backup from the server as the primary server

the configuration of the master-slave server architecture can greatly enhance the robustness of the MySQL database architecture. For example, when there is a problem with the primary server, we can manually or automatically switch to a service from the server, where the data from the server and the primary database at the time of the outage are almost identical.

This is similar NFS Storage Data is synchronized to the Backup NFS server via Inotify+rsync , except that the MySQL replication Scheme is its own tool

Use MySQL replication function to do data backup, in the hardware failure, software failure scenario, the data backup is valid, but for the implementation of the drop,delete and other statements to delete data, the backup function from the library is useless, Because the deleted statement is also executed from the server.

Application Scenario Two: master-slave server realizes read and write separation, load balancing from server

The master-Slave server architecture is available through programs ( PHP,java, etc.) or agent software (mysql-proxy,amoeba) to enable the user (client) request read-write separation, that is, from the server only to process the user's Select query request, reduce user query response time and read and write at the same time on the primary server to bring access to the pressure. The updated data (for example , Uodate,insert,delete statements) is still given to the primary server for processing, ensuring that the primary server and the server remain in real-time synchronization.

650) this.width=650; "Width=" 414 "height=" 373 "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

Scenario Three: Split multiple slave servers according to business importance

can put a few different from the server, according to the company's business to split. For example, there are slave servers that provide query services for external users , slave servers for data backup by internal DBAs, and background, scripts, log analysis, and slave servers for developers to use to provide access to company insiders. In addition to reducing the pressure on the primary server, this split can make the database independent of external user browsing, internal user business processing, and DBA personnel backup.

650) this.width=650; "Width=" 472 "height=" 309 "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

1.3 MySQLIntroduction to Master-slave replication principle

MySQL 's master-slave replication is an asynchronous replication process (although it is generally felt to be real-time), the data will be copied from one mysql database (which we call Master) to another MySQL Database (which we call Slave), the process of implementing the entire master-slave replication between master and Slave is done by a three-thread engagement. There are two threads (SQL thread and IO thread) on the Slave side, and another thread (I/O thread) on the Master side.

to implement MySQL Master-slave replication, you must first open the Master side of the Binlog record function, otherwise it will not be implemented. Because the entire replication process is essentially Slave getting the Binlog log from the Master , and then executing the acquired Binlog in the same order on Slave Various SQL operations that are logged in the log .

650) this.width=650; "Width=" 698 "height=" "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd; "alt=" Spacer.gif "/>

below for MySQL Master-slave replication principle focuses on the summary.

Master-slave replication is an asynchronous, logical, SQL statement-level replication.

When replicating, the main library has an I/O thread, with two threads, I/O, and SQL threads from the library.

The necessary condition for master-slave replication is that the main library should turn on the record Binlog function.

The Server-id for all MySQL nodes that are replicated cannot be the same.

The Binlog file only records SQL statements that have changes to the data (changes from the contents of the primary database) and does not log any query (select,show) statements.

Forget the database password

MYSQLD_SAFE--DEFAULTS-FILE=/DATA/3306/MY.CNF--skip-grant-table--user=mysql &

Then login without entering a password

Mysql-uroot-p-s/data/3306/mysql.sock

Set a password after entering the database

Updatemysql.user set Password=password (' oldboy123 ') where user= ' root ' andhost= ' localhost ';

Refresh Permissions

Flushprivileges;

1.4 MySQLmaster-Slave copy operation steps

Architectural Practices :

3306---->3307

Master ---> from

1. Open the main library Binlog, Configure Server-id

[Email protected]~]# egrep-i "Server-id|log-bin"/data/3306/my.cnf

log-bin=/data/3306/mysql-bin

Server-id= 6

Restart Service

/data/3306/mysqlrestart

From the Library

[Email protected]~]# egrep-i "Server-id|log-bin"/data/3307/my.cnf

#log-bin=/data/3307/mysql-bin

Server-id= 7

2. Main Library Create user

grantreplication slave on * * to ' rep ' @ ' 172.16.1.% ' identified by ' oldboy123 ';

Mysql>grant replication Slave on * * to ' rep ' @ ' 172.16.1.% ' identified by ' oldboy123 ';

Query OK, 0rows affected (0.04 sec)

Mysql>select user,host from Mysql.user;

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

| User |host |

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

| Root |127.0.0.1 |

| Rep | 172.16.1.% |

3. exporting data from the main library

According to what we have said , take a backup of today's points directly.

First Lock Table Flush table with read lock;

Mysql>flush table with read lock;

Query OK, 0rows Affected (0.00 sec)

mysql> showmaster status;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|mysql-bin.000001 |              120 |                  |                   | |

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

1 row in Set (0.00 sec)

Mysqldump

Cp/tar

Xtrabackup

It's the key to getting the location. [A1] sed-n ' 22p ' All_2017-06-28.sql

Master Library fully prepared

[Email protected] ~]# mysqldump-b--master-data=2--single-transaction-s/data/3306/mysql.sock-a|gzip>/data/ backup/all_$ (date+%f). sql.gz

[[Email protected] ~] #ls-L/data/backup/

Total Dosage 228

-rw-r--r--1root root 178468 June 11:11 all_2017-06-28.sql.gz

Main Library Unlocked :

mysql> unlock table;

Query OK, 0rows Affected (0.00 sec)

4. Importing fully-prepared data from a library

[[Email protected]]# cd/data/backup/

[Email protected]]# gzip-d all_2017-06-28.sql.gz

[Email protected]]# mysql-s/data/3307/mysql.sock<all_2017-06-28.sql

5. Find the location point and change master from the library

[Email protected]]# sed-n ' 22p ' All_2017-06-28.sql

--Changemaster to master_log_file=' mysql-bin.000001 ',master_log_pos=120;

Change Master from the library

Change MASTER to

Master_host= ' 172.16.1.52 ',

master_port=3306,

Master_user= ' rep ',

Master_password= ' oldboy123 ',

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=120;

Open Slave

mysql> start slave;

Query OK, 0rows affected (0.03 sec)

Mysql> show Slave status\g

Shown below indicates success

[Email protected]]# mysql-s/data/3307/mysql.sock-e "show slave Status\g" |egrep "_running| Behind_master "|head-3

Slave_io_running:yes

Slave_sql_running:yes

seconds_behind_master:0

3306 to view managed hosts

Mysql> show slave hosts;

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

| server_id | Host | Port | master_id | Slave_uuid |

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

|      7 | |         3307 | 6 |295750C8-54C1-11E7-80DD-000C29FC02EE |

|      8 | |         3308 | 6 |328E8C80-54C1-11E7-80DD-000C29FC02EE |

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

2 rows in Set (0.00 sec)

[A1]

This article is from the "Li Songyang" blog, make sure to keep this source http://lsy666.blog.51cto.com/11729318/1942931

MySQL Master-slave replication