MySQL database master-slave Replication related introduction

The company uses the Master-slave structure, on the concrete study under, here records the related content.

The benefits of replication

Read-write Detach: The primary database is responsible for writing and update data and reads from the database
Data security: Because data is replicated to and from the library, and you can stop the replication process from the library, you can perform a backup service from the library without having to interrupt the primary server
Analytics: Online data can be created on the master database and then analyzed on the slave database without performance impact on the master database
Long data: If a branch office needs copy of the primary data, you can use replication to create a local backup.
Replication type:

Statement Based Replication (SBR): Binary logs are saved as SQL statements to achieve synchronization through SQL on slave
Row Based Replication (RBR): Only copy Change row
Mixed Based Replication (MBR): Mixed type
NOTICE: This blog uses SBR (the default log format) for master-slave replication.

Master Settings considerations:

On master, you must use a binary log and set a unique ID number.
On each slave server, you must set a unique ID number in order to connect to the Maste server
(optional) Create an independent user to authenticate the primary server and replicate by accessing the binary log
Before you create a data snapshot or start the replication process, you need to log the master server's binary log location. This information is required at late slave startup
If data already exists on the master server and need to be synchronized, you need to create a snapshot of the data. (available via mysqldump or direct copying of files)
The slave server needs to be configured to fill in the master server-related information.
Some options:

If you are new master and slaves, you need only the configuration file
If the database is already running, you need to synchronize the data first
If you add slaves to an already existing primary provisioning environment, you only need to set up slaves (by copying the slave data to the new slave way to achieve the data synchronization effect)
MySQL Database related operations

Master server Configuration

1. MY.CNF Configuration

Modify the my.cnf file, enable binary logging, and set Server-id as unique number, do not follow slave repeat

[Mysqld]
Log-bin=mysql-bin
Server-id=1
NOTICE:
For good consistency, you can set up innodb_flush_log_at_trx_commit=1 and sync_binlog=1, but there is a certain performance impact.

2. Create a dedicated account mainly from

Each connection from the server to the primary server uses the MySQL user and password to create a dedicated master/slave account for security purposes.
Creating a user on the master server requires replication slave permissions for the replication dedicated user.

Mysql>create user ' username ' @ ' identified by ' slavepass ';
Mysql>grant replication Slave on *.* to ' username ' at ' address ';
3. Get Mater's binary log location

Gets the current binary log location of master, so that slave is copied from its current location

Connecting to the primary database, executing flush tables with read lock command
Mysql> FLUSH TABLES with READ LOCK;
Open another reply and use show Master status to determine the file name and location of the current binary log (you can also use the same session)

Mysql>show Master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | Test | Manual,mysql |
+------------------+----------+--------------+------------------+
4. Slave Get sync Point

If the database has previous data, you need to replicate the database with mysqldump or a snapshot of the data first

Mysqldump to create a data snapshot

Shell> mysqldump–all-databases–master-data > Dbdump.db
Using –master-data will automatically lock the table, and will export the location, open the export file, you can see that there are change master to master_log_file= ' mysql-bin.000001 ', master_log_pos= 3254350; Such a position statement does not require a manual view of master's binary log location.

If you do not use –master-data, you need to open a different terminal, input flush tables with READ lock, lock table operation, mysqldump after the end of the input

UNLOCK TABLES
Data Directory Copy mode

When the volume of data is large, the use of mysqldump efficiency is low, it is recommended to copy the original data file for migration
In order to obtain data consistency, the restricted need to close the database

Shell>mysqladmin shutdown
Copy MySQL's data file
Shell>tar Cf/tmp/db.tar./data
Slave settings

Modify the My.cnf file to set Server-id as a unique number

[Mysqld]
server-id=2
Slave server does not require the use of binary logs, but it is recommended that binary logs be enabled so that more can be done (database backup, as the main library of other slave)

Master-Slave Copy specific operation

1. The new master and slave server build

Configure master Database Properties

[Mysqld]
Log-bin=mysql-bin
Server-id=1
Start master server

Create a master-slave user
Each connection from the server to the primary server is using the MySQL user and password
Create a user on the master server
Mysql>create user ' *username* ' @ ' *address* ' identified by ' *slavepass* ';
Mysql>grant replication Slave on *.* to ' *username* ' @ ' *address* ';
Get information about master database

Connecting to the primary database, executing flush tables with read lock command

Mysql> FLUSH TABLES with READ LOCK;
Open a second reply on the master server and perform show master status to determine the current binary log file name and location

MySQL > Show MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | Test | Manual,mysql |
+------------------+----------+--------------+------------------+
Releasing the read lock on the primary server
Mysql>unlock tables;

On the slave server, configure the MySQL properties
[Mysqld]
server-id=2
Start the slave database
To set master master-Slave server configuration by performing change master on the slave database
Mysql> Change MASTER to
-> master_host= ' Master_host_name ',
-> master_user= ' Replication_user_name ',
-> master_password= ' Replication_password ',
-> master_log_file= ' Recorded_log_file_name ',
-> master_log_pos=recorded_log_position;
Note Master-slave configuration cannot use UNIX socket files, only TCP/IP

In 5.6.5 or previous versions, Slave_master_info and
Slave_relay_log_info is using the Myiam storage engine, which can be modified using the following statement (not to be modified in the runtime phase of the database)

ALTER TABLE Mysql.slave_master_info Engine=innodb;
ALTER TABLE Mysql.slave_relay_log_info Engine=innodb;
View slave Run status

Show Slave Status\g
2. Replication operations for existing data

Create a replication user
Mysql>create user ' *username* ' @ ' *address* ' identified by ' *slavepass* ';
Mysql>grant replication Slave on *.* to ' *username* ' @ ' *address* ';
If the master database does not have Server-id and binary logs set up, you need to close the MySQL database to add the related configuration
If you need to close the database, it is a good opportunity to create snapshots. Snapshot creation by copying the original data.

Get the Data Directory location
Close Database
Service MySQL Stop
Package The Data Directory
Tar Cf/tmp/tmm/db.tar./mysql
If set, gets the log location and then uses mysqldump or raw data replication for data export
You can obtain a binary location using the "Get Mater Binary Log Location" description, and then export the database using the following command:

>mysqldump--all-databases >db.dump
or use Mysqldump–all-databases–master-data > dbdump.db directly, this command does not need to obtain log location information and is included directly in the file.

Set up Slvae (refer to previous introduction)
This step differs depending on how you create the snapshot
Mysqldump Way:

Start slave and use the –skip-slave-start option to enable replication to not start the database
Import data
Mysql<fulldb.dump
Raw Data Files Method:

Extract data
Tar xvf Dbdump.tar
Chown-r Mysql:mysql mysql/
The extracted directory needs to be set to MySQL user rights, so that MySQL can connect and modify these files
Start slave, use the –skip-slave-start option
Execute the Change MASTER to statement on the slave and set the connection

Start slave thread
Mysql<start slave;
Do not forget to delete the Skip-slave-start option in MY.CNF after the startup is successful, if you add parameters through the command line, you can ignore it.

3. Increase slave

Copy a Slave that exists

Turn off the slave service
Shell> mysqladmin shutdown
Copy the Data directory (group from the raw form configuration)
Tar cf/tmp/fulldump.dump./mysql
Extract the directory on the added slave
Tar xvf fulldump.dump
To modify directory permissions:
Chown-r Mysql:mysql mysql/
Add 2 parameters to the MY.CNF, manually specify the Relay-log prefix format for the new slave, and do not use the host name as the format:

Relay-log=f8392e8f9f63-relay-bin
Relay-log-index=f8392e8f9f63-relay-bin
If you do not modify the Relay-log format as a copy of the Slave-relay-log format, MySQL will automatically use the host name as the new Slave-relay-log format, the start will be an error:

1. 
   
   
   

At last:

Read-write separation is achieved through master-slave replication, the main database is responsible for writing, from the database responsible for reading, to achieve the purpose of separation of read and write. Once thought that tall things, in fact, a layer of peeling off after that, continue to learn.