Mysql database master-slave replication deployment notes, mysql master-slave

Mysql database master-slave replication deployment notes, mysql master-slave

Principles of master-slave database replication:
The master-slave replication of a database is to copy data from the master database to the slave database. The entire replication between the master and slave requires three threads, two of which are on the slave and one on the master.
The binlog function must be enabled on the master, because the database needs to obtain the complete operation log of the master data, and then perform various operations in the execution log in its own order.

Main steps:
1. Connect the slave io thread to the master and request the operation log content after the specified location of the specified log file;
2. After the master receives the slave io thread request, it returns the content at the specified location of the specified log read in the request to the slave io thread, including the location and name of the binlog;
3. After receiving information, the slave io thread writes the received log content to the end of the relay log file on the slave end, the binlog file name and location are recorded in the master-info file, so that the next request is sent to the master;
4. The slave SQL thread will detect some new log Content in the relay log and parse it into executable query statements to be executed on the slave side, so as to achieve data consistency between the master and slave.

Configuration method:
MASTER
1. Edit the master configuration file/etc/my. cnf and add the following content to [mysqld:
Log-bin = mysql-bin # enable the mysql operation log function
Binlog_format = mixed # Master-slave replication mode, which defaults to the hybrid mode (MBR ).
Binlog_cache_size = 4 M # Set the binlog cache size
Max_binlog_size = 300 M # Set the binlog file size. If this parameter is not set, the default value is 1.1 GB.
Expire_logs_days = 3 # bin-log storage days in the master database. The bin-log generated by the master database is not automatically deleted and needs to be deleted manually.

2. Authorize the slave database on the master to create a VPC account for the replication user
Mysql> grant replication slave on *. * TO 'repl' @ '% 'identified BY 'repl ';

3. Back up the master database
When backing up master data, you must lock the master table:
Mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
Mysql> reset master; // refresh the database
Query OK, 0 rows affected (0.00 sec)
If you do not exit the terminal (otherwise the lock table will become invalid), enable a terminal to directly package and compress data files or use mysqldump to export data:
# Mysqldump-uroot-h 127.0.0.1-opt-r xxxxxx>/data/mysql/backup/XXXXXX. SQL
# Cd/(www.111cn.net) data/mysql/backup/

4. Back up the database to the slave Database
# Tar-czvf XXXXXX.tar.gz XXXXXX. SQL
# Scp XXXXXX.tar.gz root@10.7.70.42:/data/mysql/backup
You can import the database to another slave machine in the same way. After the import is complete, run the UNLOCK command on the Master command terminal:
Mysql> unlock tables;
Mysql> show processlist;
Mysql> show master statusG; # view the bin-log and id of the master server

5. Set the slave host
Edit the/etc/my. cnf configuration file of the slave database. The preceding configuration parameters are the same as those of the master database and are adjusted according to the hardware memory:
# Binlog_format = mixed
# Required unique id between 1 and 2 ^ 32-1
# Defaults to 1 if master-host is not set
# But will not function as a master if omitted
Server-id = 2 // slave id. Do not repeat it.
Read_only = 1
Binlog_format = mixed
Binlog_cache_size = 4 M // you can specify the binlog cache size.
Max_binlog_size = 300 M // you can specify the maximum size of the binlog file.

Create a new database on the slave host and import the data of the master database:
Mysql> create database XXX;
Mysql> use XXX;
Mysql> set names utf8;
Mysql> source/XXXXXX/XXX. SQL

Set the master of the slave database:
Mysql> change master to MASTER_HOST = 'xx. XX. XX. xx', // ip address of the MASTER database
-> MASTER_USER = 'repl ',
-> MASTER_PASSWORD = 'repl ',
-> MASTER_LOG_FILE = 'mysql-bin.000001 ', // set this parameter based on the binlog and id of the master server.
-> MASTER_LOG_POS = 107 ;//
After setting, enable the slave service:

Mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
Mysql> show slave statusG; // displays the status of slave.
From the output result, if Slave_IO_Running and Slave_ SQL _Running are both Yes, the configuration is successful.
Add multiple slave databases online:
Principle: If the slave database is directly added to the master database, the mysql service will be stopped to lock the table, thus affecting the normal workflow,
Therefore, online addition of slave databases can achieve database replication by backing up old slave databases. The change master can be used again to add the slave database online.
From: http://www.111cn.net/database/mysql/59749.htm

How many databases can be synchronized at most for Mysql master-slave replication? What are the best suggestions?

In theory, master-slave replication supports an infinite number of slave databases. In practice, it is affected by the bandwidth and read/write capacity of the server.
Please refer to the suggestions in the mysql official manual:
Theoretically, you can use a single master server/multiple slave server to expand the system by adding more slave servers until the network bandwidth is used up, or your update load has grown to a point that the master server cannot handle.

Before you get the benefits, you need to know the query mode to determine how many slave servers can be used and how much performance can be improved for your site, in addition, the relationship between the throughput and write (max_writes) throughput of a typical master server and the read (read per second, or max_reads) from the slave server should be determined based on experience. Through a hypothetical system with replication, this example provides a very simple computing result.

Assuming that the system load includes 10% write and 90% read, and the benchmark test shows that max_reads is 1200-2 × max_writes. In other words, if there is no write operation, the system can perform 1,200 read operations per second. The average write operation is twice the average read operation time, and the relationship is linear. We assume that the master server has the same performance as each slave server, and we have one master server and N slave servers. For each server (master server or slave server), we have:

Reads = 1200-2 × writes

Reads = 9 × writes/(N + 1) (read is separated, but written to all servers)

9 × writes/(N + 1) + 2 × writes = 1200

Writes = 1200/(2 + 9/(N + 1 ))

The final equation indicates the maximum write operations of N slave servers. Assuming that the maximum possible read rate is 1,200 times per minute, the ratio of read operations to write operations is 9.

The following conclusions can be obtained from the above analysis:

· If N = 0 (this indicates there is no replication), the system can process about 1200/11 = 109 write operations per second.

· If N = 1, 184 write operations are performed per second.

· If N = 8, 400 write operations are performed per second.

· If N = 17, 480 write operations are performed per second.

MySQL master-slave database backup can be synchronized to be consistent with the master database after the database is interrupted?

When the host of the master database is shut down, the data can only be read but not written. After the host of the slave database is shut down, the data is still readable and writable. This is not easy to understand. If you have some knowledge about the lua script, you can modify the mysql-proxy read/write splitting script.
However, when the master database is shut down, it cannot be accessed, and the slave database is still readable and writable. No read/write splitting is required. This is the basic capability of master-slave replication, and the slave database is only used for backup. When the slave database is disabled, the slave IO thread automatically copies the binary logs of the host from the location where the binary logs are located. The slave SQL thread reads and executes the SQL statements in these binary logs.
In short, no special configuration is required, which is the basic capability of master-slave replication.