MySQL Master-slave replication

MySQL Master-slave Replication (master-slave) and read-write separation (mysql-proxy) practices

As the most widely used free database in the world, MySQL believes that all engineers engaged in system operations must have contacted. However, in the actual production environment, the single MySQL as a separate database is completely unable to meet the actual needs, whether in security, high availability and high concurrency and other aspects.

Therefore, in general, it is through the master-slave Replication (Master-slave) to synchronize the data, and then through the read-write separation (mysql-proxy) to improve the database of the concurrency load of such a scenario for deployment and implementation.

As shown in the following:

Here are my notes in the process of actual work, shared here, for your reference.

First, the installation and configuration of MySQL
Specific installation process, it is recommended to refer to my article: http://heylinux.com/archives/993.html
It is worth mentioning that my installation process is the source package compiled and installed, and all the configuration and data are unified planning to the/opt/mysql directory, so after the installation on one server, you can package the entire MySQL directory, and then upload to other servers to unpack, you can immediately use.

Second, MySQL master-slave replication
Scenario Description:
Primary database server: 192.168.10.130,mysql is already installed and has no application data.
From the database server: 192.168.10.131,mysql is installed and no application data is applied.

2.1 Operations on the primary server
Start the MySQL service
/opt/mysql/init.d/mysql start

Managing MySQL server via command line login
/opt/mysql/bin/mysql-uroot-p ' New-password '

Authorization to 192.168.10.131 from the database server
Mysql> GRANT REPLICATION SLAVE on * * to ' rep1 ' @ ' 192.168.10.131 ' identified by ' password ';

Querying the primary database state
Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 261 | | |
+------------------+----------+--------------+------------------+

Records the values of FILE and Position, which need to be used in the subsequent operations from the server.

2.2 Configuration from the server
Modify the configuration file from the server/opt/mysql/etc/my.cnf
Modify Server-id = 1 to Server-id = 10, and make sure that the ID is not used by another MySQL service.

Start the MySQL service
/opt/mysql/init.d/mysql start

Managing MySQL server via command line login
/opt/mysql/bin/mysql-uroot-p ' New-password '

Executing a synchronous SQL statement
Mysql> Change Master to
Master_host= ' 192.168.10.130 ',
Master_user= ' Rep1 ',
master_password= ' Password ',
Master_log_file= ' mysql-bin.000005 ',
master_log_pos=261;

Start slave synchronization process after correct execution
mysql> start slave;

Master-Slave synchronization check
Mysql> Show Slave Status\g
==============================================
1. Row *******************
Slave_io_state:
master_host:192.168.10.130
Master_user:rep1
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000005
read_master_log_pos:415
relay_log_file:localhost-relay-bin.000008
relay_log_pos:561
relay_master_log_file:mysql-bin.000005
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
............... To omit a number of ......
Master_server_id:1
1 row in Set (0.01 sec)
==============================================

The value of slave_io_running and slave_sql_running must be yes to indicate that the status is normal.

If the primary server already has application data, the following processing is required for master-slave replication:
(1) The primary database for the lock table operation, do not let the data to write again action
Mysql> FLUSH TABLES with READ LOCK;

(2) View Primary database status
Mysql> Show master status;

(3) record the value of FILE and Position.
Copy the data file of the master server (the entire/opt/mysql/data directory) to the slave server, it is recommended to compress the tar archive and then upload it to the server.

(4) Canceling the primary database lock
Mysql> UNLOCK TABLES;

2.3 Verifying the master-slave copy effect

Operations on the primary server
Create a database on the primary server first_db
mysql> CREATE DATABASE first_db;
Query Ok, 1 row affected (0.01 sec)

Create a table on the primary server FIRST_TB
Mysql> CREATE TABLE FIRST_TB (ID int (3), name char (10));
Query Ok, 1 row Affected (0.00 sec)

Insert a record in table FIRST_TB on the primary server
mysql> INSERT INTO FIRST_TB values (001, ' myself ');
Query Ok, 1 row Affected (0.00 sec)

Viewing from a server
mysql> show databases;
=============================
+--------------------+
| Database |
+--------------------+
| Information_schema |
| first_db |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
5 rows in Set (0.01 sec)
=============================
Database first_db has been automatically generated

Mysql> Use first_db
Database chaged

Mysql> Show tables;
=============================
+--------------------+
| tables_in_first_db |
+--------------------+
| FIRST_TB |
+--------------------+
1 row in Set (0.02 sec)
=============================
database table FIRST_TB has also been created automatically

Mysql> select * from FIRST_TB;
=============================
+------+------+
| ID | name |
+------+------+
| 1 | Myself |
+------+------+
1 rows in Set (0.00 sec)
=============================
The record is already there.

Thus, the entire MySQL master-slave replication process is complete, next, we do MySQL read and write separation of the installation and configuration.

MySQL Master-slave replication