Shulou(Shulou.com)06/01 Report--

Preface: why should MySQL be in charge of replication (separation of read and write)?

Generally speaking, if the read and write of the database are operated in the same database server, the performance of the business system will be degraded.

In order to improve the performance of the business system and optimize the user experience, we can reduce the load on the master database through master-slave replication (read-write separation).

And if the master database is down, the business system can be quickly switched to the slave database to avoid data loss.

Second, the difference between MySQL master-slave replication (read-write separation) and cluster:

1. Master-slave replication (read-write separation): generally, two or more database servers are required (one for writing data, one for synchronizing master data and for data query operations).

Limitations:

(1) after master-slave replication is configured, the same table can only be written to one server. If a write operation is performed on the slave, and then the master also manipulates the table, or causes the master to be out of sync; it is said that it can be configured in master-master mode, but I haven't studied it yet.

(2) the master database server is down, so you need to manually switch the business system to the slave database server. High availability cannot be achieved (unless a high availability solution is made by deploying keepalive).

2. The cluster is composed of N database servers, the data writing and query are randomly sent to any database server, and other database servers will automatically synchronize the operation of the database.

The downtime of any database will not have a big impact on the whole cluster.

Limitations: after testing, I know that the current mysql cluster version (MySQL Cluster) can only synchronize the data of the NDB storage engine, but not INNODB or other MySQL storage engines. This also led me to abandon the application of this solution in business systems.

Third, to get back to the point, let's start the master-slave replication tutorial of MySQL5.6.12:

1. There are two ways for MySQL5.6 to start master-slave replication: log-based (binlog) and GTID-based (global transaction identifier).

It should be noted that temporary tables are not supported in GTID mode! So don't consider this approach if your business system uses temporary tables, at least the GTID replication of the latest version of MySQL5.6.12 does not support temporary tables.

So the main purpose of this tutorial is to tell you how to copy master and slave through binlog!

2. The official MySQL Replication tutorial provided by MySQL:

Http://dev.mysql.com/doc/refman/5.6/en/replication.html

It is strongly recommended that you read this official tutorial (you need some English reading ability! No, just google and then read after translation.

3. Preparatory work:

(1) before configuring MySQL master-slave replication (read-write separation), you need to install MySQL5.6 on both master and slave servers.

(2) the latest version of MySQL5.6 GA is MySQL5.6.12.

Personally recommend Linux (RedHat/CentOS 6.4) source code compilation and installation. For more information, please see this tutorial: RedHat/CentOS source code compilation and installation of MySQL5.6.12

(3) Note:

(a) if you need to use it in a production environment, don't rush to do mysql startup when installing MySQL. It is recommended to delete the / usr/local/mysql/mysql.cnf generated by mysql initialization, and then put your optimized mysql configuration file my.cnf under / etc.

(B) it is recommended that the two main and standby servers are on the same local area network and the two database networks need to be interconnected.

(4) my environment:

Master database IP:192.168.100.2

IP:192.168.100.3 from the database

4. Modify the configuration file of the master database:

1 [mysqld] 2server-id=13log-bin=mysqlmaster-bin.log4sync_binlog=15# Note: the following parameter needs to be changed to about 70% of the server memory: 6innodb_buffer_pool_size = 512M7innodbflushregistered registered attainable trxstores, 18sqlstores modewritten STRICTRANSlocations TABLESMagazine NOUTOTOBINECONTERINE SUBSTITUTIONdivision NONTOBINEVALUTION division NONTOTOINESUBSTITUTION division NONTOTOINESUBSTITUTION1 of the server memory needs to be modified to 6innodb_buffer_pool_size = 512M7innodbflushbookbookcards naming 110 logbooks bindings trustworthy functions

Restart mysql after modification:

1# / etc/init.d/mysql restart

5. Modify the configuration file of the slave database (server-id can be configured as a number greater than 1):

1 [mysqld] 2server-id=23log-bin=mysqlslave-bin.log4sync_binlog=15# Note: the following parameter needs to be changed to about 70% of the server memory: 6innodb_buffer_pool_size = 512M7innodbflushregistered registered attainable trxstores, 18sqlstores modewritten STRICTRANSlocations TABLESMagazine NOUTOTOBINECONTERINE SUBSTITUTIONdivision NONTOBINEVALUTION division NONTOTOINESUBSTITUTION division NONTOTOINESUBSTITUTION1 of the server memory needs to be modified to 6innodb_buffer_pool_size = 512M7innodbflushbookbookcards naming 110 logbooks bindings trustworthy functions

Restart mysql after modification:

1# / etc/init.d/mysql restart

6. SSH logs in to the main database:

(1) create an account on the master database for master-slave replication (192.168.100.3 replace your slave database IP):

"mysql-uroot-p2mysql > GRANT REPLICATION SLAVE ON *. * TO 'repl'@'192.168.100.3' IDENTIFIED BY' repl'

(2) Primary database lock table (data re-insertion is prohibited to obtain the binary log coordinates of the primary database):

1mysql > FLUSH TABLES WITH READ LOCK

(3) then clone a SSH session window and open the MySQL command line in this window:

Mysql-uroot-p2mysql > SHOW MASTER STATUS 3. File 4 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 5 minutes- -+ 6 | mysqlmaster-bin.000001 | 332 | 7 years Murray Murray- -+ 81 row in set (0.00 sec) 9mysql > exit

In this example, the binary log file is mysqlmaster-bin.000001, and the location is 332. record these two values, which you will use later.

(4) create a data snapshot on the primary database using the mysqldump command:

1#mysqldump-uroot-p-h227.0.0.1-P3306-all-databases-- triggers-- routines-- events > all.sql2# will prompt you to enter the root password of the mysql database. After entering, if the current database is not large, the export will be completed soon.

(5) unlock the table locking operation of step (2) master data:

1mysql > UNLOCK TABLES

7. Log in to the slave database with SSH:

(1) upload the snapshot all.sql of the master database backed up in the previous step to a path of the slave database through FTP, SFTP or other means, for example, I put it in the / home/yimiju/ directory

(2) Import the snapshot of the master from:

Cd / home/yimiju2# mysql-uroot-p-h227.0.0.1-P3306

CHANGE MASTER TO MASTER_HOST='192.168.100.2',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysqlmaster-bin.000001',MASTER_LOG_POS=332;3# then starts the replication thread from the database: 4mysql > START slave;5# and then queries the database's slave status: 6mysql > SHOW slave STATUS\ Yes. If both of the following parameters are Yes, the master-slave configuration is successful! 8Slave_IO_Running: Yes9Slave_SQL_Running: Yes

(4) next, you can create databases, tables, insert data on the master database, and then see if these operations are synchronized from the slave database.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.