Configure master and slave nodes in centos

MySQL master-slave solution introduces the main functions of the MySQL master-slave solution:
Read/write splitting allows the database to support larger concurrency. Especially important in reports. Some Report SQL statements are very slow, leading to table lock and affecting front-end services. If the front-end uses the master node and the report uses the slave node, the SQL statements of the report will not cause the front-end lock and ensure the front-end speed.
Develop the advantages of different table engines. Currently, the MyISAM Table query speed is slightly faster than that of InnoDB, while the write concurrency of InnoDB is better than that of MyISAM. Therefore, we can use InnoDB as the master to handle highly concurrent writes and use the master as the slave to accept queries. Or create a full-text index in MyISAM slave to solve the weakness of InnoDB without full-text index.
For Hot Standby, the data of slave and master is "quasi-real-time" synchronization.
Preparation. Install two MySQL instances first
Configure the master. Find the my. CNF file and modify it:

server-id                = 1log_bin                        = /var/log/mysql/mysql-bin.logexpire_logs_days        = 10max_binlog_size         = 100Mbinlog_do_db                = DB_AdidasFootballbinlog_do_db                = DB_CodeBuilderbinlog_ignore_db        = test

The server-ID must be 1.
Binlog_do_db is the database to be copied. Binlog_ignore_db is the DB that ignores replication. To add a database, add a corresponding row.
Restart the master database and run the check:

Mysql> show Master Status; # Check whether the master is started. + ------------------ + ---------- + Ratio + ---------------- + | file | position | binlog_do_db | ratio | + -------------------- + ---------- + ratio + ---------------- + | mysql-bin.000002 | 1087 | percent, db_codebuilder | test | + ------------------ + ---------- + response + ---------------- + 1 row in SET (0.00 Sec) mysql> show variables like "% log %"; # You need to see such a line, BINLOG is Enabled: log_bin | on

Create a user for slave on the master

mysql> grant replication slave, reload, super on *.* to ‘slave‘@‘10.*‘ identified by ‘123456‘;

Note: 10. * here the IP address is 192.168.1.2 of the slave database.

In this way, the host configuration is complete.

Configure slave

Server-id = 2 # Whatever number, multiple slave must be unique. # Log_bin =/var/log/MySQL/mysql-bin.log # The BINLOG of slave does not need to be enabled again. Comment out. Master-host = 192.168.0.3 # Master's ipmaster-user = slave # in the above operation, the created user name master-Password = 123456 # in the above operation, the created Password

Restart slave and check salve status

SLAVE: mysql> show slave status; # many columns

SLAVE: mysql> stop slave; # Stop slave replication first. SLAVE: mysql> change master to master_host = '2017. 168.1.2 ', master_user = 'slave', master_password = '000000', master_log_file = 'mysql-bin.000014 ', master_log_pos = 123456; # update master slave: mysql> Start slave; # Start Server Load balancer replication. SLAVE: mysql> show slave status; # Check the slave status. If there are two Yes values, the operation is successful.

Next, copy the host data. This process is complicated: note the machines in each step.

SLAVE: mysql> stop slave; # Stop slave replication first. MASTER: mysql> flush tables with read lock; # Lock all tables on the master server and prohibit writing. MASTER: mysql> show Master Status; # Or the above statement, view and record the file mysql-bin.000002, position 1087 + ------------------ + ---------- + ratio + ---------------- + | file | position | binlog_do_db | ratio | + -------------------- + ---------- + ratio + ---------------- + | mysql-bin.000002 | 1087 | percent, db_codebuilder | test | + ---------------- + --- ------- + ---------------------------------- + ------------------ + 1 row in SET (0.00 Sec) [email protected]: ~ $ Mysqldump adidasfootball> adidasfootball. SQL # export the database data in the command line. Here is the bash operation: MASTER: mysql> unlock tables; # unlock after the export is complete. The master can continue running. [Email protected]: ~ $ Mysql adidasfootball <adidasfootball. SQL # import dB data in the slave command line. Here it is a bash operation: slave: mysql> change master to-> master_log_file = 'mysql-bin.000002 ', # change the data recorded here to master_log_pos = 1087; # Here, slave: mysql> Start slave;

Complete.
Note: The slave must have the required database before synchronization. Otherwise, the synchronization will be ignored.

Configure master and slave nodes in centos