MySQL master-slave replication Environment setup

Preparing the installation package
Download the latest version of the MySQL client and server installation package from the official Oracle website, MySQL is currently available as a whole package download, no separate installation package to download, direct download

V45446-01 The overall package, which contains the latest version of the MYSQL5.6.19 client and server installation package.

MYSQL-CLIENT-ADVANCED-5.6.19-1.EL6.X86_64.RPM (v45446-01)
MYSQL-SERVER-ADVANCED-5.6.19-1.EL6.X86_64.RPM (v45446-01)

In Redhat installation, also prepare the MySQL off dependency package:
perl-5.10.1-136.el6.x86_64.rpm
libaio-0.3.107-10.el6.x86_64.rpm
mysql-lib5-5.1.71-1.el6.x86_64.rpm

One, MySQL installation

1, installation system, configuration IP, configuration/etc/hosts file
/etc/hosts file
192.168.1.21 REPLM
192.168.1.22 REPLSL

Configure IP
[Email protected] mysql]# Cat/etc/sysconfig/network-scripts/ifcfg-eth0
Device=eth0
Hwaddr=08:00:27:a7:0e:e2
Type=ethernet
Onboot=yes
Nm_controlled=yes
Bootproto=static
ipaddr=192.168.1.21
netmask=255.255.255.0
gateway=192.168.1.1
Ipv6init=no
Userctl=no

2. Upload RPM installation package
3, shut down the firewall (need to shut down, otherwise synchronization may not be successful, I encountered this issue, the following details the problems encountered)
Service Iptables stop--stop firewall
Chkconfig iptables off-Configuring the system reboot does not start the firewall

4. Configure the Yum source

mount/dev/cdrom/mnt/--Mount Optical Drive

Vi/etc/yum.repos.d/mysql.repo

[MySQL]
Name=mysql
Baseurl=file:///mnt
Gpgcheck=0
Enable=1

5, delete the system comes with the mysql-libs* package, or install mysql-server-advanced-5.6.19-1.el6.x86_64.rpm error, delete the command as:
Yum-y Remove mysql-libs-*

6, install RPM package, command for RPM
RPM-IVH mysql-devel-advanced-5.6.19-1.el6.x86_64.rpm
RPM-IVH mysql-client-advanced-5.6.19-1.el6.x86_64.rpm
RPM-IVH mysql-server-advanced-5.6.19-1.el6.x86_64.rpm

7. Start MySQL

Service MySQL Start
Set turn on self-boot
Chkconfig MySQL on

8. Log in to MySQL and set the root user password

After the installation of MySQL, the default password will be placed in the/root/.mysql_secret file, the first time you log in, use the password to login:
Cat/root/.mysql_secret
# The random password set for the root user at Thu Jul 16:23:52 (local time): Geeuu0krfqkatly0

Log in to MySQL using the default password:
Mysql-uroot-p geeuu0krfqkatly0

Set the new root password, assuming that it is set to 111111

mysql> SET PASSWORD = PASSWORD (' 111111 ');

Set the root user login permission so that users can log on remotely using the client:

mysql> use MySQL;
Mysql> GRANT All privileges on * * to [e-mail protected] "%" identified by "111111";
mysql> Update user Set password = password (' 111111 ') where user= ' root ';

Refresh permissions for permission modification to take effect:
Mysql>flush privileges;

Note: Log on remotely and verify that root user access is not a problem, and then start configuring Master-slave replication.

Second, MySQL master configuration

1. Close the service first:
Service MySQL Stop

To modify the MySQL configuration file, master-slave replication requires that the machine Server-id not be the same, from the library must open Relay-log:

1) Modify master configuration file
Vi/etc/my.cnf
Server-id = 111
Log-bin=/var/lib/mysql/binlog/mysql-bin.log
Relay-log=/var/lib/mysql/relaylog/mysql-relay.log

To create a log path:
Mkdir/var/lib/mysql/binlog
Mkdir/var/lib/mysql/relaylog

Modify Path Permissions:
Chown Mysql:mysql/var/lib/mysql/binlog-r
Chown Mysql:mysql/var/lib/mysql/relaylog-r

2) Modify the configuration file from slave
Vi/etc/my.cnf
Add the following content:
Server-id = 222
Log-bin=/var/lib/mysql/binlog/mysql-bin.log
Relay-log=/var/lib/mysql/relaylog/mysql-relay.log

To create a log path:
Mkdir/var/lib/mysql/binlog
Mkdir/var/lib/mysql/relaylog

Modify Path Permissions:
Chown Mysql:mysql/var/lib/mysql/binlog-r
Chown Mysql:mysql/var/lib/mysql/relaylog-r

2. Master and Salve start MySQL instance

Service MySQL Start

3, login master and slave set up a dedicated copy account Repl, master and slave are executed

Set the master-slave account, named Repl, set its access to the 192.168.1 network segment, password 123456:

MySQL > Grant replication slave,replication Client on * * to ' repl ' @ ' 192.168.1.% ' identified by ' 111111 ';
Refresh permissions:
MySQL > Flush privileges;

View the current location of the main library (main library execution)
Mysql> Show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
|      mysql-bin.000003 |              120 |                  |                   | |
+------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)

3, starting from the library synchronization, synchronization location is the current location of the main library (from the Library execution):
Mysql>change Master to master_host= ' 192.168.100.6 ', master_user= ' repl ',
Master_password= ' 123456 ', master_log_file= ' mysql-bin.000003 ', master_log_pos=235;

Start slave
mysql> start slave;

Check whether the copy is normal from the library (both slave_io_running and slave_sql_running are yes, must be yes, no other state is copied)

Mysql> Show Slave Status\g
1. Row ***************************
Slave_io_state:waiting for Master to send event
master_host:192.168.1.21
Master_user:repl
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000003
read_master_log_pos:120
relay_log_file:replsl-relay-bin.000002
relay_log_pos:283
relay_master_log_file:mysql-bin.000003
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
replicate_ignore_db:
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:120
relay_log_space:457
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:
Replicate_ignore_server_ids:
master_server_id:111
Master_uuid:7b4e0e11-05dc-11e5-b3be-080027a70ee2
Master_info_file:/var/lib/mysql/master.info
sql_delay:0
Sql_remaining_delay:null
Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it
master_retry_count:86400
Master_bind:
Last_io_error_timestamp:
Last_sql_error_timestamp:
MASTER_SSL_CRL:
Master_ssl_crlpath:
Retrieved_gtid_set:
Executed_gtid_set:
auto_position:0
1 row in Set (0.00 sec)

Configuration complete

Third, testing

1. Create database and table on master side, and insert data

mysql> CREATE DATABASE Zxmdb;
Query OK, 1 row Affected (0.00 sec)

mysql> use Zxmdb;
Database changed
mysql> CREATE TABLE t1 (ID int,name char (10));
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO T1 values (1, ' zxm ');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO T1 values (1, ' zxm ');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO T1 values (1, ' zxm ');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO T1 values (2, ' King ');
Query OK, 1 row affected (0.04 sec)

2, slave-side verification

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| Test |
| Zxmdb |
+--------------------+
5 rows in Set (0.00 sec)

Mysql> Use Zxmdb
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed
Mysql> select * from T1;
+------+------+
| ID | name |
+------+------+
| 1 | ZXM |
| 1 | ZXM |
| 1 | ZXM |
| 2 | King |
+------+------+
4 rows in Set (0.00 sec)

Iv. Summary

Encounter problems
1, the installation process error, unable to install mysql-server-advanced-5.6.19-1.el6.x86_64.rpm, the reason is that Redhat comes with the installation of mysql-libs-

5.1.66-2.el6_3.x86_64, some files already exist, causing the installation to fail.
Workaround: Remove mysql-libs-5.1.66-2.el6_3.x86_64
Yum-y Remove mysql-libs-*
Then the installation succeeds

2, slave side Execution show slave status output slave_io_running:connecting, resulting in the data can not be copied;
Last_io_error:error connecting to master ' [email protected]:3306 '-retry-time:60 retries:36
Analysis Reason: Slave end cannot connect to master side
1) REPL user rights are not correct
2) port is occupied
3) Firewall blocking

1) test Connection, error
Mysql-u repl-p111111-h 192.168.1.21
Warning:using a password on the command line interface can is insecure.
ERROR 2003 (HY000): Can ' t connect to MySQL server on ' 192.168.1.21 ' (113)

2) Close firewall test connection, normal connection, then restart slave, copy normal

1. my.cnf configuration file skip-networking, Bindaddress is configured

Skip-networking causes all TCP/IP ports to be not monitored and other clients cannot connect to this MySQL server using the network
Bindaddress This parameter is a request connection that the MySQL server responds only to the configured IP address

MySQL master-slave replication Environment setup