MySQL Master-Slave construction

MySQL master-Slave Building summary

I. prerequisites for MySQL master-slave database construction

1.mysql version must be consistent

2. change the firewall and SELinux policies to prevent test failures

two . Modify the master server configuration file , file location /etc/my.cnf

[Mysqld]

Datadir=/home/bymiao/lib/mysql

Socket=/home/bymiao/lib/mysql/mysql.sock

Lower_case_table_names=1

Character_set_server=utf8

Query_cache_type=1

query_cache_limit=10485760

port=3306

log-bin=/home/bymiao/mysqldata/mysql-bin // Place the path of the file whatever you want, but remember that the owner of the directory must be MySQL, or MySQL will fail to start

Log-bin-index=/home/bymiao/mysqldata/mysqld-bin

server-id=1 // must be set Server-id, and is unique within the same cluster

[Mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

three . Modify the file location from the server configuration file /etc/my.cnf

[Mysqld]

..... Partial Omission ...

Log-bin=/home/bymiao/mysqldata/mysql-bin

server-id=2

Four All servers Restart after configuration is complete

Service mysqld Restart

Five Create an authorized account on the primary server

Mysql> GRANT REPLICATION SLAVE on * * to ' master ' @ ' percent ' identified by ' 123456 ';

for the sake of security, generally without root direct authorization, new Administrator account to authorize.

Six. Log in to the master server and query the Master status

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|      mysql-bin.000002 |              120 |                  |                   | |

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

Seven Configure the slave server

1.mysql> Change master to master_host= ' z primary server IP address ', master_user= ' just the primary server authorized Account ', Master_  Password= ' just the primary server authorized Password ', master_log_file= ' mysql-bin.000002 ', master_log_pos=120; --just check the master server status for the files and locations

2. after the configuration is complete, start the copy function from the server mysql> start slave;

3. Check the replication status from the server, the two marked lines appear the Yes character indicates the configuration is successful

Mysql> Show Slave STATUSG

1. row***************************

Slave_io_state:waiting Formaster to send event

master_host:192.168.1.54

Master_user:master

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000002

read_master_log_pos:120

relay_log_file:mysqld-relay-bin.000003

relay_log_pos:283

relay_master_log_file:mysql-bin.000002

Slave_io_running:yes

Slave_sql_running:yes

Problems that may occur during the configuration process:

Last_io_error:fatal error:the slave i/othread stops because master and slave have equal MySQL server UUIDs; Theseuuids must is different for replication to work.

This is due to the fact that the two MySQL uuid are the same:

Because you may be lazy when you install the system, another cloned machine,

Workaround: Enter the directory

Vim/home/bymiao/lib/mysql/auto.cnf

Just change the UUIDand restart the database.

Eight Test

Test whether the master-slave database can synchronize data

Test method:

1. Create a library test on the primary server , if there is a library, go to another name of the library can also;

2. Check for the library from the library;

The library shows that the master-slave data can be synchronized to meet our requirements and experiment successfully.

This article is from the "10696204" blog, please be sure to keep this source http://10706204.blog.51cto.com/10696204/1693814

MySQL Master-Slave construction