MySQL master-slave replication Environment setup

Source: Internet
Author: User
Tags local time mysql client

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.