Mysql Master-Slave Synchronization Configuration Example

* Two Linux servers, one master from the database, synchronize the data from the primary database

* System: Centos 6.4-64bit

* Primary (Master) database server IP address: 192.168.100.200

* FROM (Slave) database server IP address: 192.168.100.201

* This experiment was installed with the system's own RPM package to provide MySQL service

1. Install MySQL service on two servers respectively

# yum-y Install MySQL Mysq-server

# service Mysqld Start

# Chkconfig Mysqld on

2. Configuring the Primary Database

All libraries and tables can be synchronized by default

# vi/etc/my.cnf #添加如下参数

Log-bin=mysqld-bin #开启binlog日志, must be turned on

Server_id=200 #数据库ID值, the only, general use native IP address host bit

Binlog-do-db=test #设置从服务器可以同步的库test, you can write more than one line

Binlog-ignore-db=mysql #设置不可以同步的库mysql

# Service Mysqld Restart #重启服务

3. permission to connect itself from the database and have copy data in the primary database

Log on to the primary server and authorize the username to be slaveuser, the password is 123456

Mysql>grant replication Slave on * * to [e-mail protected] identified by ' 123456 ';

4. Verify that the authorization is successful from the server

If you can log on to the master database from the database, the authorization is successful.

# mysql-h192.168.100.200-uslaveuser-p123456

Mysql> Show grants; #查看用户slaveuser自己被授的权限

+---------------------------------------------------------------------------+

| Grants for [email protected] |

+---------------------------------------------------------------------------+

| GRANT REPLICATION SLAVE on *. * to ' slaveuser ' @ ' 192.168.100.201 ' identified by PASSWORD ' * 6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 ' |

+---------------------------------------------------------------------------+

5. See which servers are on the primary database server

Mysql> show Slave hosts; #从数据库还没配置, so I can't see it.

6. Configuration from the database

# vi/etc/my.cnf #添加如下参数

Log-bin=mysqld-bin #开启binlog日志

server_id=201 #数据库ID号

Report-host=localhost #向主服务器报告自己主机名, the primary server will know which server is being queried

report-host=192.168.100.201 #向主服务器报告自己IP, the primary server will know which server is being queried

slave-net-timeout=60 #主从网络中断时, wait 60 seconds for automatic connection

Read_only=1 #只读模式 (sync with super privilege user exception)

Note: If you want to limit the synchronization of the library, the parameter format is as follows

replicate-do-db= "Database name" #允许同步的库

replicate-ignore-db= "Database name" #不允许同步的库

# Service Mysqld Restart

After restarting from the database, at this point, you can log on to the primary database to see which servers are on the primary database server, as follows

Mysql> show Slave hosts;

+-----------+-----------------+------+-------------------+-----------+

| server_id | Host | Port | Rpl_recovery_rank | master_id |

+-----------+-----------------+------+-------------------+-----------+

| 201 | 192.168.100.201 |       3306 |    0 | 200 |

+-----------+-----------------+------+-------------------+-----------+

7. Log in to the master database to see the Binlog and offsets currently used in the primary database state

Mysql> Show master status; #显示主数据库服务器的状态

+-------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+-------------------+----------+--------------+------------------+

| mysqld-bin.000004 |           |            | |

+-------------------+----------+--------------+------------------+

File and postion corresponding values mysqld-bin.000004 and 334 to be recorded, the following steps to use

Description

File #正在使用的binlog日志

Position #正在使用binlog日志的偏移量, meaning to start recording from offset position 120

binlog_do_db #显示准许同步复制的库

binlog_ignore_db #显示不准许同步复制的库

Executed_gtid_set #跟binlog日志相似, are used for master-slave synchronization, default off, two with one can.

Open Gtid: #没特殊要求不开启

Vim/etc/my.cnf

Gtid_mode=on

8. Connect the primary database from the database (operate from the database)

Mysql>change Master to

->master_host= "192.168.100.200", #主数据库的IP地址

->master_user= "Slaveuser", #主数据库上授权的用户

->master_password= "123456", #授权用户的密码

->master_log_file= "localhost-bin.000001", #上一步中File字段里的binlog日志

->master_log_pos=120; #上一步中Postion字段里的偏移量

After operating the above command, 4 files are generated under/var/lib/mysql/

Ls/var/lib/mysql

Master.info #连接主数据库服务器的配置信息

Relay-log.info #中继日志信息

localhost-relay-bin.000001 #中继日志文件, the log file that the master copied is called the relay.

Localhost-relay-bin.index #中继日志文件列表

9. Log in from the database, open the Server_io and server_sql process from the database

Mysql>start slave; #开启I0和SQL (stop using: Stop slave)

Mysql>show slave status\g; #查看I0和SQL进程状态

Slave_io_running:yes #必须开启状态

Slave_sql_running:yes #必须开启状态

> Only io-running and sql_running are yes, master and slave can synchronize

> After start slave, if slave_io_running or no status, check again after 1 minutes

10, verify master and slave can synchronize

Log in to the master database and create a table under library test student

Mysql>use test;

Mysql>create table Student (name char (3), birthday date); #创建表student

mysql> desc Student; #查看表结构

+----------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+---------+------+-----+---------+-------+

| name | CHAR (3) |     YES | |      NULL | |

| Birthday | Date |     YES | |      null| |

+----------+---------+------+-----+---------+-------+

Log in from the database to see if the table student created by the primary database is synchronized, as shown below, indicating that the master and slave can synchronize.

Mysql>use test;

Mysql> Show tables;

+----------------+

| Tables_in_test |

+----------------+

| Student |

+----------------+

Over, thank you.

This article is from the "Dave-Technology blog" blog, please be sure to keep this source http://davewang.blog.51cto.com/6974997/1858470

Mysql Master-Slave Synchronization Configuration Example