Master slave synchronization, master server addition, deletion and modification, and slave server search

1. Definition of MySQL master-slave synchronization

Master-slave synchronization enables data to be copied from one database server to other servers. When copying data, one server acts as the master and the other servers act as slave servers
Because replication is asynchronous, the slave server does not need to be connected to the master server all the time. The slave server can even connect to the master server intermittently through dial-up
Through the configuration file, you can specify to copy all databases, a database, or even a table on a database

2. Benefits of using master-slave synchronization

By adding slave servers to improve the performance of the database, writing, updating and deleting are performed on the master server, and reading functions are provided on the slave server. The number of slave servers can be dynamically adjusted to adjust the performance of the whole database
Improve data security - because the data has been copied to the slave server, the slave server can terminate the replication process. Therefore, it can be backed up on the slave server without damaging the corresponding data of the master server
Real time data is generated on the master server and analyzed on the slave server to improve the performance of the master server

3. Master slave synchronization mode

mysql Asynchronous replication, and MySQL Cluster Is synchronous replication. There are many master-slave synchronization methods, but there are two core methods, Statement Based Replication(SBR)be based on SQL Statement replication, the other is Row Based Replication(RBR)Row based replication can also be used Mixed Based Replication(MBR). stay mysql5.6 In, the default is SBR. and mysql 5.6.5 And later versions are based on global transaction identifiers(GTIDs)For transactional replication. When used GTIDs Can greatly simplify the replication process because GTIDs It is completely transaction based. As long as a transaction is committed on the master server, the slave server will execute the transaction.

SBR: when binary log is used, the master server will write SQL statements to the log, and then the slave server will execute the log. This is SBR. In mysql5 Versions before 1.4 can only use this format. Using SBR will have the following advantages

Strengths:
1. Smaller log files
2. All statements are recorded and can be used for future audit
Disadvantages:
1. Statements using the following function cannot be copied correctly: load_file(); uuid(), uuid_short(); user(); found_rows(); sysdate(); get_lock(); is_free_lock(); is_used_lock(); master_pos_wait(); rand(); release_lock(); sleep(); version();
2. The following warning messages in the log cannot be copied correctly: [Warning] Statement is not safe to log in statement format
3. Or show warnings appears in the client
4. The Insert... select statement executes a large number of row level lock tables
5. The Update statement executes a large number of row level locks to scan the entire table
RBR: the master server writes the row changes of the table into the binary log as events. The master server copies the events representing the row changes to the slave service and uses RBR

Strengths:

All data changes are replicated, which is the safest way to replicate
Fewer row level lock tables

Disadvantages:

The log will be big
You cannot audit executed sql statements by viewing the log, but you can use mysqlbinlog
– Base64 output = decode rows -- verbose to view data changes

MBR: both SBR and RBR are used. SBR is used by default

4. Master slave synchronization mechanism

The master-slave synchronization between Mysql servers is based on the binary log mechanism. The master server uses the binary log to record the changes of the database, and the slave server keeps the data consistent with the master server by reading and executing the log file
When binary logs are used, all operations of the master server are recorded (binary logs), and then a copy of the log (relay logs) is received from the slave server. The slave server can specify which kind of events in the log are executed (for example, only inserting data or only updating data). All statements in the log will be executed by default.
Each slave server will record information about the binary log: file name and processed statements, which means that different slave servers can execute different parts of the same binary log, and the slave server can connect or disconnect from the server at any time.
The master server and each slave server must be configured with a unique ID number (there is a server ID configuration item under the [mysqld] module of my.cnf file). In addition, each slave server also needs to configure the ip address, log file name and location in the log of the master server it wants to connect to through the CHANGE MASTER TO statement (this information is stored in the database of the main server)

5. Configuration of master-slave synchronization

Keep the database state of the three nodes consistent

Prepare three servers

node1   #master
node2   #slave
node3	#slave

5.1 configuring the master server

Back up the database on the primary server

On the primary server, the binary logging mechanism must be enabled and a separate id must be configured

#Close the mysql service of the primary server and restart it after configuration
[root@node1 /etc/my.cnf]


[mysqld]
#Open binary log
log-bin=mysql-bin

#Configure the id of the primary server
server-id=1

The master server queries the binary log name and offset location of the master (the value of File is the File name of the binary log currently used, and Position is the location information in the log)
```
show master status;
```

5.2 configuring slave servers

Find my. From the server CNF configuration file, add server ID
```
[root@node2 /etc/my.cnf]


[mysqld]

server-id=2
```

Restart mysql, open a MySQL session, and execute synchronous SQL statements (the host name of the main server, login credentials, and the name and location of binary files are required):

mysql>
		CHANGE MASTER TO
        MASTER_HOST='10.10.10.21',
        MASTER_USER='root',
        MASTER_PASSWORD='root123ROOT...',
        MASTER_LOG_FILE='mysql-bin.000001',
        MASTER_LOG_POS=156;

Restore the data files backed up from the primary server (because the position queried by the primary server is the latest offset of the primary server, it must be restored to the same state as the primary server first)
Start the slave synchronization process
```
mysql>
	start slave;
```

View slave status

mysql>
	show slave status\G; 
    -- appear   Slave_IO_Running: Yes   Slave_SQL_Running: Yes  Is the successful startup

6. Other configurations of master-slave synchronization

master After binary logging is enabled, the operations of all tables in all libraries are recorded by default. You can specify that only the operations of the specified database or even the specified table are recorded through configuration. For details, see mysql Configuration file[mysqld]The following options can be added or modified:

-- Which databases are not synchronized  
binlog-ignore-db = mysql  
binlog-ignore-db = test  
binlog-ignore-db = information_schema  
  
-- Only which databases are synchronized, in addition, others are not synchronized  
binlog-do-db = game

7. Notes on MySQL master-slave replication

During the master-slave replication of mysql, if the replication of functions or stored procedures is involved, it needs to be configured in [mysqld]

#Enabling the creation of stored procedures and functions also generates event logs
log_bin_trust_function_creators=true

In mysql of linux, you need to configure the case of the table. Ignore it, or the table may not be found

#Configuration database and table names are not case sensitive
lower_case_table_names=1

8. Launch a new database server

Found my. On the new server CNF configuration file, add server ID
```
[root@node3 /etc/my.cnf]


[mysqld]

server-id=3
```