Master-Slave Database schema Configuration

In Web application system, database performance is one of the main causes of system performance bottleneck. Especially in large-scale systems, database clustering has become one of the necessary configurations. The main benefits of clustering are: query load, database copy backup, and so on.

The MySQL database supports the master-slave replication of the database and therefore has its unique advantages in clustering. Many domestic and foreign large-scale website architecture system, have adopted MySQL master-slave database configuration to realize query load, database hot-standby and other functions. I also covered this requirement in the actual Web project, where I will make a brief summary of how to configure the implementation.

1. Experimental environment

Main Library: Ubuntu ip:192.168.1.189

From library: Ubuntu ip:192.168.1.188

2. Primary database Configuration

A. Modify the configuration file/etc/mysql/my.cnf

Any MySQL database server can be configured as a clustered master server, open the MySQL configuration file and add the following two lines to the configuration file:

Server-id = 1

Log-bin = Binlog_repl

BINLOG-DO-DB = Test//Set the database that needs to be synchronized, and if you need to set more than one, add multiple lines of this statement.

Note: MySQL is a binary log file for master-slave database replication, so you must turn on the log function, that is, the above log-bin; In addition, in the cluster, each database server needs to specify a unique ID, where we specify 1.

To authorize a user who can replicate to the primary database, execute the following command:

Grant Replication Slave on * * to ' slave ' @ '% ' identified by ' 123 ';

After successful execution, restart MySQL.

B. Lock the database and back up

Mysql>flush tables with read lock;

Back up the database, transfer to the data directory from the database/var/lib/mysql;

C, with show master status; command to view the primary database state

+--------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+--------------------+----------+--------------+------------------+
|      binlog_repl.000001 | 106 |                  Test | |
+--------------------+----------+--------------+------------------+

Records the values of file and position.

D, the main database unlock: Unlock tables;

3, from the database configuration

A. Modify the configuration file/etc/mysql/my.cnf

Add the following code under MYSQLD:

server-id=2

master-host=192.168.1.189

Master-user=slave

Master-password=123

After saving, restart the MySQL service.

B, set the slave parameter, start

Execute the slave stop command under MySQL to stop the slave service;

Mysql> Change Master to

Master_host= ' 192.168.1.189 ',

Master_user= ' slave ',

-master_password= ' 123 ',

Master_log_file= ' binlog_repl.000001 ',

master_log_pos=106;

Note: The value of Master_log_file,master_log_pos here is the same as the value of master. Otherwise, you will not be able to synchronize.

Execute the slave start command to start the service.

4. Verifying synchronization

Run show slave status \g from the database;

If you can see:

Slave_io_running:yes

Slave_sql_running:yes

Indicates that data synchronization for the master-slave database has been successfully initiated.

Execute INSERT statement in primary database insert into user values (' kangqing ', ' 1234567890 ');

When you execute a query from the database, you can see that the data that is inserted into the primary database has been synchronized to the database table.

I have been in the configuration of the time there are such problems:

The values for slave_io_running and slave_sql_running are yes, but cannot be synchronized with the primary database. When you insert a record in the primary database, there is no change from the database table, and you can see this error when you execute the show slave status:

Last_sql_error:error ' Table ' user ' is the read only ' on query. Default database: ' Test '. Query: ' INSERT into user values (' Gaga ', ' 5436897 ') '

The estimate is a permission issue.

Workaround: Modify the permissions of the/var/lib/mysql folder to grant Read and write permission to the Mysql.mysql user.

Master-Slave Database schema Configuration