MySQL master copy configuration steps Classic example

IP planning:

MySQL Master: 192.168.0.100
MySQL from: 192.168.0.200

MY.CNF Configuration

In the main MySQL [mysqld] Item, in addition to configuring the relevant parameters to configure the necessary three items, the simplest example is:

[Mysqld]
User=mysql
Pid-file=/var/run/mysqld/mysqld.pid
Socket=/var/run/mysqld/mysqld.sock
Basedir=/usr
Datadir=/var/lib/mysql
Tmpdir=/tmp
Log-bin=master-bin
Log-bin-index=master-bin.index
Server-id=1

The appropriate configuration of the standby machine is as follows:

[Mysqld]
User=mysql
Pid-file=/var/run/mysqld/mysqld.pid
Socket=/var/run/mysqld/mysqld.sock
Basedir=/usr
Datadir=/var/lib/mysql
Tmpdir=/tmp
server-id=2
Relay-log=slave-relay-bin
Relay-log-index=slave-relay-bin.index
Note: The relay item is configured with a relay log and a relay index file, and the host name is used by default if the binary log files and the relay log file names are not specified. Recommended for customization.

Restart MySQL for the above configuration to take effect.

Create a new user for replication permissions on main mysql:

mysql> grant replication Slave on *.* to ' repl ' @ '% ' identified by ' mysqls ';
A new user named Repl with a password of MYSQLS. Of course, for security reasons, you can change the% of the sentence to the specified host.
Query OK, 0 rows Affected (0.00 sec)
When the previous prompt appears, the new user is successful.

Mysql> Show master status;
+-------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+-------------------+----------+--------------+------------------+
|      master-bin.000004 |              224 |                  | |
+-------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)

Specify information about the replication host on the standby and specify the binaries and log numbers to be replicated

mysql> Change Master to master_host= ' 192.168.0.100 ', master_user= ' repl ', master_password= ' Mysqls ', master_log_file = ' master-bin.000004 ', master_log_pos=224;

Master_host the value of this entry suggests using the hostname, because if the IP address change is encountered, the hostname is generally not changed, so it is still in effect. But the IP has changed, here is still the use of IP, will cause the host can not find the corresponding main device.
Enable slave state

mysql> start slave;

Use show slave status to see if the configuration was successful:
Mysql> Show slave status;

The result is too much, I will not replicate here, but the host look at the two below, if you see the value is yes, indicates that it has been successful.

Slave_io_running:yes
Slave_sql_running:yes

To verify the synchronization of data:
Master MySQL to build the library, build the table, and insert test data:

mysql> CREATE DATABASE Repl;
Mysql> CREATE table my (id int (3), name char (10));
mysql> INSERT INTO FIRST_TB values (001, ' Test ');
The above operation in the host each operation step, can be seen on the standby also has a corresponding library, table, the value of the increase.

Just a tough one. using batch processing on master MySQL, add 1000 data:
Test data

#!/bin/bash
For ((i=0;i<1000;i++))
Todo
M= ' TR-DC a-z-a-z</dev/urandom |head-c 5 '
Mysql-uroot-ptest repl-e "INSERT into my values ($i, ' $m ')"
Done
On the standby machine:

Corresponding to the operation of the above host, you can view the corresponding data.

mysql> show databases;
mysql> use REPL;
Mysql> Show tables;
Mysql> select * from I;

The above operations are tested in a completely new installation environment for both the host and the standby. When you want to use it in a production environment, you synchronize the databases that you want to synchronize with each other. And if the master data is still in use, refresh the database and pin the database.

Mysql>flush tables with read lock;

First, use mysqldump to export all the data and import it into the standby machine.
When you're done, unlock the main MySQL.

Mysql>unlock tables;

Of course, if the data volume is very large, the use of mysqldump processing is very slow, it is recommended to use replication physical files for data synchronization.
The synchronization operation is the same as above.

The above MySQL operation synchronization is a full synchronization of all libraries, as well as MySQL, information_schema data of these two libraries. If you want to specify a synchronized database and an ignored database, use four parameters:

Binlog-do-db=repl
Binlog_ignore_db=mysql
Binlog_ignore_db=information_schema
Replicate-do-db=repl
Replicate-wild-ignore-table=mysql
Replicate-wild-ignore-table=information_schema

Of course, the above four parameters are used when there is exquisite, improper use or risk. Go back and make a summary!