How to setup Mysql Replication Master-Slave ?(MySQL主从复制)

只是简单的记录主从的配置过程和一些常用排错方法 ,我本次安装使用的是mysql 5.6.16,和之前的老版本配置可能有些不同。

Step 1 - Install and Configure MySQL on Server Master

MYSQL软件安装和5.5一样http://www.anbob.com/archives/2177.html

$ vi /etc/my.cnf

server-id = 1
log-bin = mysql-bin
binlog-do-db = loadtest

TIP:
The first of those lines is to uniquely identify our particular server, in our replication configuration. The second line indicates the file in which changes to any mysql database or table will be logged. The third line indicates which databases we want to replicate between our servers. You can add as many databases to this line as you'd like. The article will use a single database named "loadtest" for the purposes of simplicity.On the master, you can control which databases to log changes for by using the --binlog-do-db and --binlog-ignore-db options to control binary logging,On the slave side, decisions about whether to execute or ignore statements received from the master are made according to the --replicate-* options that the slave was started with.Database-level options (--replicate-do-db, --replicate-ignore-db) are checked firs
Now we need to restart mysql:

[root@mysqlp ~]# service mysql restart

Create a test database and test user authorization.

mysql> create database loadtest;
mysql> create user 'replicator'@'%' identified by 'replicator';
mysql> grant replication slave on *.* to 'replicator'@'%'; 
mysql> use loadtest
mysql> create table test(id int);
mysql> insert into test values (1),(2);

Step 2 - Install and Configure MySQL on Server Slave

vi /etc/my.cnf
# Replication Slave Server
server-id = 2
log-bin = mysql-bin
binlog-do-db = loadtest
#binlog-do-db = zjncws
service mysql restart
mysql> create database loadtest;
mysql> create user 'replicator'@'%' identified by 'replicator';
mysql> grant replication slave on *.* to 'replicator'@'%';

Step 3 - Completing Replication

-- ON Master DB
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1309 | loadtest | | |
+------------------+----------+--------------+------------------+-------------------+

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000001' from 721;
+------------------+------+------------+-----------+-------------+-------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+------------+-----------+-------------+-------------------------------------------------------------+
| mysql-bin.000001 | 721 | Query | 1 | 846 | use `loadtest`; DROP TABLE `test` /* generated by server */ |
| mysql-bin.000001 | 846 | Query | 1 | 953 | use `loadtest`; create table test(id int) |
| mysql-bin.000001 | 953 | Query | 1 | 1040 | BEGIN |
| mysql-bin.000001 | 1040 | Query | 1 | 1153 | use `loadtest`; insert into test values (1),(2) |
| mysql-bin.000001 | 1153 | Xid | 1 | 1184 | COMMIT /* xid=104 */ |
| mysql-bin.000001 | 1184 | Query | 1 | 1309 | use `loadtest`; DROP TABLE `test` /* generated by server */ |
+------------------+------+------------+-----------+-------------+-------------------------------------------------------------+
-- create a store procedure to generate some rows in Master DB
mysql> DELIMITER $$
mysql> CREATE PROCEDURE ABC()
->
-> BEGIN
-> DECLARE a INT Default 1 ;
-> simple_loop: LOOP
-> insert into table1 values(a);
-> SET a=a+1;
-> IF a=100000 THEN
-> LEAVE simple_loop;
-> END IF;
-> END LOOP simple_loop;
-> END $$
Query OK, 0 rows affected (0.00 sec)
mysql> DROP PROCEDURE IF EXISTS abc;
mysql> CALL `ABC`();
Query OK, 1 row affected (1.03 sec)

-- ON Slave DB
mysql>slave stop;
mysql>CHANGE MASTER TO MASTER_HOST = '', MASTER_USER = 'replicator', MASTER_PASSWORD = 'replicator', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 846;
mysql>slave start;
mysql> show create table test;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
`id` integer DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------+
Show slave status\\G
show relaylog events in "Relay_Log_File" from Relay_Log_Pos limit n;
show relaylog events in 'mysql-slave-relay-bin.000019' from 1146600 LIMIT 5;
1.Skip n number of transactions：
mysql>slave stop;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 #跳过一个事务
mysql>slave start
if table not exists in Slave, Manual create table :
set sql_log_bin=OFF; create table XXX; set sql_log_bin=ON;

mysqlbinlog --start-date="2005-12-25 11:25:56" binlog.000003 | mysql -h server-salve -u root -p
There are several possibly causes for slave setup breakage. This time it was the local relay log file (mysqld-relay-bin.xxxx) that was out of sync.