MySQL Master-slave replication installation configuration 2-Master from the online environment configuration

Host
172.16.0.21
172.16.0.22
centos6.2
Use yum to install MySQL separately

Create a new library for MySQL on 21

CREATE DATABASE ' replytest1 '/*!40100 DEFAULT CHARACTER SET UTF8 * *;

Building a table

CREATE TABLE ' replytest1 '. ' Test1 ' (
' ID ' INT UNSIGNED not NULL auto_increment,
' Test1col ' VARCHAR () NULL,
PRIMARY KEY (' id '));

Inserting data

INSERT into ' replytest1 ' test1 ' (' Test1col ') VALUES (' 1′);
INSERT into ' replytest1 ' test1 ' (' Test1col ') VALUES (' 2′);

——————————————————-

Modifying the Master library configuration file

Server-id = 1//database ID number, represented as master at 1, where master_id must be a positive integer value between 1 and 232–1;
Log-bin=mysql-bin//enable binary log;
Binlog-do-db=data//binary database name to sync; can write multiple lines
Binlog-ignore-db=mysql//unsynchronized binary database name; I heard it was a hassle after this sync.
Log-slave-updates//write the updated records into binary files;
Slave-skip-errors

Create a user responsible for replication

# # #GRANT all privileges on *.* to reply@172.16.0.1 identified by ' reply ' with GRANT OPTION;
Grant replication Slave on *.* to test@192.168.2.119 identified by ' ******** '

Flush privileges;

Lock Main Library

FLUSH TABLES with READ LOCK;

Export primary table data

Unlock and check the current master status and where to copy it

UNLOCK TABLES; Show master status;

Set from library

Modify configuration file

#从库设置
server-id=2
master-host=172.16.0.1
Master-user=reply
Master-password=reply
master-connect-retry=600 #如果发现主服务器断线, the time lag of reconnection;
Replicate-ignore-db=mysql
Replicate-ignore-db=test
Log-slave-update
Slave-skip-errors
#从库设置结束

Restart MySQL

To reset the primary database by executing the Stop Slave,reset Master command from the server

mysql> stop Slave;
Query OK, 0 rows affected, 1 Warning (0.00 sec)

mysql> Reset Master;

Change Master to master_host= ' 172.16.0.21′,master_user= ' reply ', master_password= ' reply ', master_log_file= ' mysql-bin.000001′, master_log_pos=106;

Slave start;

Mysql> Show Slave STATUSG
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:172.16.0.21
Master_user:reply
master_port:3306
connect_retry:600
master_log_file:mysql-bin.000001
read_master_log_pos:106
relay_log_file:mysqld-relay-bin.000002
relay_log_pos:251
relay_master_log_file:mysql-bin.000001
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
Replicate_ignore_db:mysql,test
Replicate_do_table:
Replicate_ignore_table:
Replicate_wild_do_table:
Replicate_wild_ignore_table:
last_errno:0
Last_error:
skip_counter:0
exec_master_log_pos:106
relay_log_space:407
Until_condition:none
Until_log_file:
until_log_pos:0
Master_ssl_allowed:no
Master_ssl_ca_file:
Master_ssl_ca_path:
Master_ssl_cert:
Master_ssl_cipher:
Master_ssl_key:
seconds_behind_master:0
Master_ssl_verify_server_cert:no
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:
1 row in Set (0.00 sec)