MySQL Master Copy Build

MySQL Master Copy Build

1. Build

1.1 Master

Master configuration (MY.CNF)

[mysqld] log-bin=/usr/local/mysql-5.6.16/log/mysql-binserver_id=1

Start Master

./bin/mysqld_safe--defaults-file=/usr/local/mysql-5.6. /my.cnf--user=mysql &

Create a user for replication

> Grant Replication  on *. *  to ' Rep1 '@'192.168.80.102'by'rep1' ;

Back up the data and restore it to the slave server
--Set read lock, data cannot be updated

>  with read lock;

--see where to start copying

Mysql>show master status;+------------------+----------+--------------+------------------+-------------------+| File             |Position|binlog_do_db|binlog_ignore_db|Executed_gtid_set|+------------------+----------+--------------+------------------+-------------------+|Mysql-Bin.000001 |       - |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+

--Transfer data

Tar SSH 192.168. 80.102 " Tar-xzf--c/usr/local/mysql-5.6.16 "

--Release lock

> unlock tables;

1.2 Slave
Slave configuration (MY.CNF)

[mysqld] server_id=2      # The only Skip-slave-start # between master and slave do not start when the slave copy process starts

Start slave

./bin/mysqld_safe--defaults-file=/usr/local/mysql-5.6. /my.cnf--user=mysql &

Configuring Replication Parameters

Mysql>Change Master to     -Master_host='192.168.80.101',     -Master_port=3306,     -Master_user='Rep1',     -Master_password='Rep1',     -Master_log_file='mysql-bin.000001',     -Master_log_pos= -;

To start slave io and SQL threads

> start slave;

Check

> Show Processlist \g

The following information should appear:
State:waiting for Master to send event
State:slave have read all relay log; Waiting for the slave I/O thread to update it

Or

> show slave status \g

The following information should appear:
Slave_io_state:waiting for Master to send event
Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it


2. Testing
2.1 Master

>  Use  >> CREATE TABLE as SELECT *   from MySQL. user;

2.2 Slave

>>Select* from T1;

3. Additional

3.1 Primary replication startup options
(1) log-slave-updates
This parameter needs to be used with Log-bin to configure whether the update operation from the server is written binlog. Closed by default. If you set up a chained copy, and the server is also the primary server of the other server, you need to turn this option on so that it binlog from the server to get its synchronization.

(2) Read-only
On: Only superuser update operation is accepted from the server
OFF: Accept update operations from the server for normal users

MySQL Master Copy Build