MySQL master-Slave synchronization configuration

MySQL's master-slave replication requires at least two MySQL services, of course, MySQL services can be distributed on different servers, or you can start multiple services on a single server.

First make sure the MySQL version on the master and slave server is the same.

I. Install and deploy MySQL

1. Install Server side: Yum Install Mysql-server

Yum Install Mysql-devel

Install client: Yum install MySQL

2. Start the service

Service mysqld Start

3. After installation, execute the following command to initialize the configuration, you will be asked to set the password:

/usr/bin/mysql_secure_installation

4. Set allow remote connection to the MySQL server

Login to Mysql:mysql-uroot-ptest123

Mysql-uroot- ptest123   #账号root, password test123welcome to the MySQL Monitor.  

At this point, such as through the Navicat connection is a failure. You need to set the user table below the MySQL library:

Mysql> Show databases;+--------------------+| Database           |+--------------------+| information_schema | | CMDB               | | mysql              | | performance_schema | | Test               Use MySQL; Update user set host= '% ' where user= ' root ' ;

Although the update failed, it was actually a successful update. Set allow any host connection.

If you enter the MySQL report this error: Access denied for user (using Password:yes)

is resolved as follows:

1), close MySQL

Service Mysqld Stop

2), Shielding permissions

Mysqld_safe--skip-grant-table

3), another terminal is executed as follows:

[[Email protected] ~] #mysql-u rootmysql>delete from user where user= '; Mysql>flush privileges; #这个一定要执行, otherwise the terminal error before closing will reproduce mysql>\q

Second, configure MySQL master-slave synchronization

Prepare two virtual machines for testing, such as installing the MySQL environment on top and turning on the MySQL service

Main master:192.168.8.10

From slave:192.168.8.11

1. Configure the Main library:

1), authorization to the database server

Mysql>grant REPLICATION SLAVE on * * to ' rep1 ' @ ' 192.168.8.11 ' identified by ' test123456 '; mysql>flush privileges;

2), modify the main library configuration file, open Binlog, and set the Server-id, each time you modify the configuration file to restart the MySQL service will not take effect

Vim/etc/my.cnf

Add the following to the configuration file [Mysqld]:

[Mysqld] Log -bin=/var/lib/mysql/binlogserver-id=1binlog-do-db = CMDBDatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock ...

The ID number of the server-id:master end;

Log-bin: Synchronized log path and file name, it must be noted that this directory if MySQL has permission to write (I am lazy here, directly under the datadir below);

BINLOG-DO-DB: The name of the database to synchronize

You can also display a database with settings that are not synchronized:

binlog-ignore-db = mysql out of sync MySQL library and test library
BINLOG-IGNORE-DB = Test

After modifying the configuration file, restart the service: Services mysqld restart

If startup fails, pass Cat/var/log/mysqld.log | Tail-30 view the log of MySQL startup failure, looking for a solution from the log content.

3), view the primary server current binary log name and offset, the purpose of this operation is to start from the database, starting from this point of data recovery

Mysql> Show master status; +---------------+----------+--------------+------------------+| File          | Position | binlog_do_db | binlog_ignore_db |+---------------+----------+--------------+------------------+| binlog.000001 |     1304 | CMDB         |                  | +---------------+----------+--------------+------------------+1 row in Set (0.00 sec)

The master server is configured.

2, configuration from the library

1), it is a matter of course to start from the configuration file, add the following configuration in/ETC/MY.CNF:

[Mysqld] Server -id=2master-host=192.168.8.10master-user=rep1master-password=test123456master-port=3306replicate-do-db= the CMDB...

Restart times wrong: Mysqld:unknown variable ' master-host=

Explains that MySQL does not recognize these variables, and the web has been collected, because mysql5.5+ version master-slave replication does not support these variables, you need to set the command from the library:

Change MASTER to master_host= ' 192.168.8.10 ', master_port=3306, master_user= ' rep1 ', master_password= ' test123456 ', Master_log_file= ' binlog.000001 ', master_log_pos=1304; #后面两个参数的值与主库保持一致

2), start the slave process

mysql> slave start; Query OK, 0 rows affected (0.04 sec)

3), check the status of the slave, if the following two key value is yes, it means that the configuration is correct:

Slave_io_running:yes

Slave_sql_running:yes

Updating data from library waiting for main library ... Waitin for Master to send event ...

Third, the synchronization of the main library has data from the library

Main Library Operations:

1. Stop data Update operations for the main library

Mysql>flush tables with read lock;

2, the new terminal, to generate a backup of the primary database (export database)

Mysqldump-uroot-ptest123 CMDB > Cmdb.sql

3. Upload the backup file to the library

SCP Cmdb.sql [Email protected]:/root/

4. Unlocking the main library

Mysql>unlock tables;

From library operations:

1. Stop Slave from the library

Mysql>slave stop;

2. New Database CMDB

Mysql> CREATE DATABASE CMDB default CharSet UTF8;

3. Import data

4. View the database and data from the library

Mysql> Show databases;+--------------------+| Database           CMDB               | | mysql              | | performance_schema | | Test               |+--------------------+

At this point, the master-slave library data is exactly the same, if the main library to make the deletion operation, from the library will be automatically synchronized operation.

MySQL master-Slave synchronization configuration