MySQL主从同步多实例配置完整版(编译+配置过程)

May 7, 2017Comments

mysqlMaster：1.1.1.22,mysqlMaster.t4x.org 2.6.32-573.22.1.el6.x86_64,CentOS release 6.7
mysqlSlave：1.1.1.101,mysqlSlave.t4x.org 3.10.0-327.13.1.el7.x86_64,CentOS Linux release 7.2.1511
基础环境：(多实例配置)

[root@mysqlMaster ~]# yum install vim gcc gcc-c++ wget ncurses-devel net-tools -y
[root@mysqlMaster ~]# yum install openssl openssl-devel pcre-devel cmake libaio* library* ncurses-* -y
[root@mysqlMaster tools]# wget http://mirrors.sohu.com/<span class='keyword-be keyword-inner'><a href="https://note.t4x.org/database/" title="database,mysql" target="_blank">MySQL</a></span>/MySQL-5.5/mysql-5.5.49.tar.gz
[root@mysqlMaster tools]# tar -zxf mysql-5.5.49.tar.gz  
[root@mysqlMaster tools]# cd mysql-5.5.49   
[root@mysqlMaster mysql-5.5.49]# cmake . -DCMAKE_INSTALL_PREFIX=/byrd/service/mysql-5.5.49 -DMYSQL_UNIX_ADDR=/byrd/service/mysql-5.5.49/tmp/mysql.sock -DMYSQL_DATADIR=/byrd/service/mysql-5.5.49/data -DWITH_DEBUG=0 -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_ZLIB=system -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci  -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1
[root@mysqlMaster mysql-5.5.49]# make && make install
[root@mysqlMaster mysql-5.5.49]# mkdir /data/{3306,3307}/data -p
[root@mysqlMaster mysql-5.5.49]# chown -R mysql:mysql /data/
[root@mysqlMaster mysql-5.5.49]# groupadd -r mysql -g 27    #设置mysql用户组(-r系统组也就是0-500，centos7为0-1000)
[root@mysqlMaster mysql-5.5.49]# useradd -r mysql -u 27 -g mysql -d /data/ -s /sbin/nologin    #色和在系统用户，默认不会设置home目录
[root@mysqlMaster mysql-5.5.49]# ln -s /byrd/service/mysql-5.5.49 /usr/local/mysql5
[root@mysqlMaster mysql-5.5.49]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3306/data --user=mysql
[root@mysqlMaster mysql-5.5.49]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3307/data --user=mysql
[root@mysqlSlave /]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3308/data --user=mysql
[root@mysqlSlave /]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3309/data --user=mysql
[root@mysqlMaster 3306]# cat /data/3306/my.cnf | grep -v ^
[client]
port    = 3306
socket  = /data/3306/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user    = mysql
port    = 3306
socket  = /data/3306/mysql.sock
basedir = /usr/local/mysql5
datadir = /data/3306/data
[root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3306/my.cnf &
[root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &
[root@mysqlMaster 3306]# netstat -tunlp|grep mysql
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      13416/mysqld        
tcp        0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      13265/mysqld   
[root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysql -uroot -p -S /data/3306/mysql.sock   #首次空密码
[root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysql -uroot -p'admin123' -S /data/3306/mysql.sock 
[root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysql -uroot -p'admin456' -S /data/3307/mysql.sock 
[root@mysqlMaster /]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3306/my.cnf &
[root@mysqlMaster /]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &
mysql> update mysql.user set password=PASSWORD('admin123') where user='root';
mysql> flush privileges;

[root@mysqlMaster ~]# yum install vim gcc gcc-c++ wget ncurses-devel net-tools -y

[root@mysqlMaster ~]# yum install openssl openssl-devel pcre-devel cmake libaio* library* ncurses-* -y

[root@mysqlMaster tools]# wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.49.tar.gz

[root@mysqlMaster tools]# tar -zxf mysql-5.5.49.tar.gz

[root@mysqlMaster tools]# cd mysql-5.5.49

[root@mysqlMaster mysql-5.5.49]# cmake . -DCMAKE_INSTALL_PREFIX=/byrd/service/mysql-5.5.49 -DMYSQL_UNIX_ADDR=/byrd/service/mysql-5.5.49/tmp/mysql.sock -DMYSQL_DATADIR=/byrd/service/mysql-5.5.49/data -DWITH_DEBUG=0 -DEXTRA_CHARSETS=all -DWITH_READLINE=1 -DWITH_ZLIB=system -DWITH_SSL=system -DWITH_EMBEDDED_SERVER=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1

[root@mysqlMaster mysql-5.5.49]# make && make install

[root@mysqlMaster mysql-5.5.49]# mkdir /data/{3306,3307}/data -p

[root@mysqlMaster mysql-5.5.49]# chown -R mysql:mysql /data/

[root@mysqlMaster mysql-5.5.49]# groupadd -r mysql -g 27 #设置mysql用户组(-r系统组也就是0-500，centos7为0-1000)

[root@mysqlMaster mysql-5.5.49]# useradd -r mysql -u 27 -g mysql -d /data/ -s /sbin/nologin #色和在系统用户，默认不会设置home目录

[root@mysqlMaster mysql-5.5.49]# ln -s /byrd/service/mysql-5.5.49 /usr/local/mysql5

[root@mysqlMaster mysql-5.5.49]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3306/data --user=mysql

[root@mysqlMaster mysql-5.5.49]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3307/data --user=mysql

[root@mysqlSlave /]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3308/data --user=mysql

[root@mysqlSlave /]# /usr/local/mysql5/scripts/mysql_install_db --basedir=/usr/local/mysql5/ --datadir=/data/3309/data --user=mysql

[root@mysqlMaster 3306]# cat /data/3306/my.cnf | grep -v ^

[client]

port = 3306

socket = /data/3306/mysql.sock

[mysql]

no-auto-rehash

[mysqld]

user = mysql

port = 3306

socket = /data/3306/mysql.sock

basedir = /usr/local/mysql5

datadir = /data/3306/data

[root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3306/my.cnf &

[root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &

[root@mysqlMaster 3306]# netstat -tunlp|grep mysql

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 13416/mysqld

tcp 0 0 0.0.0.0:3307 0.0.0.0:* LISTEN 13265/mysqld

[root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysql -uroot -p -S /data/3306/mysql.sock #首次空密码

[root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysql -uroot -p'admin123' -S /data/3306/mysql.sock

[root@mysqlMaster 3306]# /usr/local/mysql5/bin/mysql -uroot -p'admin456' -S /data/3307/mysql.sock

[root@mysqlMaster /]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3306/my.cnf &

[root@mysqlMaster /]# /usr/local/mysql5/bin/mysqld_safe --defaults-file=/data/3307/my.cnf &

mysql> update mysql.user set password=PASSWORD('admin123') where user='root';

mysql> flush privileges;

配置环境：(主从同步配置)
3306主库:

[root@mysqlMaster /]# /usr/local/mysql5/bin/mysql -uroot -p'admin123' -S /data/3306/mysql.sock 
mysql> select user,host from mysql.user;       
+------+--------------------+
| user | host               |
+------+--------------------+
| root | 127.0.0.1          |
| root | ::1                |
|      | localhost          |
| root | localhost          |
|      | mysqlslave.t4x.org |
| root | mysqlslave.t4x.org |
+------+--------------------+
mysql> delete from mysql.user;   
Query OK, 6 rows affected (0.00 sec)      
mysql> select * from mysql.user\G
mysql> grant all privileges on *.* to system@'localhost' identified by 'admin123' with grant option;
mysql> flush privileges;
mysql> select * from mysql.user\G  
*************************** 1. row ***************************
                  Host: localhost
                  User: system
              Password: *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C
mysql> create database da3306t21 default character set utf8 collate utf8_general_ci;    
mysql> drop database test;
mysql> show databases;    
+--------------------+
| Database           |
+--------------------+
| information_schema |
| da3306t21          |
| mysql              |
| performance_schema |
+--------------------+

mysql> grant replication slave on *.* to byrd@'1.1.1.101' identified by 'passwd123';     #授权从库ip       
mysql> select user,host from mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| byrd   | 1.1.1.101 |
| system | localhost |
+--------+-----------+

mysql> flush tables with read lock;    #锁表

mysql> show variables like '%timeout%';     #查询超时时间，默认28800
mysql> show variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| interactive_timeout        | 28800    |
| wait_timeout               | 28800    |
+----------------------------+----------+

mysql> show master status;     #获取bin_log位置点
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      188 |              |                  |
+------------------+----------+--------------+------------------+
[root@mysqlMaster /]# /usr/local/mysql5/bin/mysqldump -usystem -p'admin123' -S /data/3306/mysql.sock --events -B -A | gzip > /tmp/all.sql.gz
mysql> show master status;    #查询位置是否偏移（-F 除外）
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      188 |              |                  |
+------------------+----------+--------------+------------------+

[root@mysqlMaster /]# scp -P22 /tmp/all.sql.gz root@1.1.1.101:/tmp
root@1.1.1.101's password: 
all.sql.gz              100%  148KB 148.2KB/s   00:00 

[root@mysqlMaster /]# egrep "server-id|log" /data/3306/my.cnf    
log-bin = /data/3306/data/mysql-bin
server-id = 1

[root@mysqlMaster /]# /usr/local/mysql5/bin/mysql -uroot -p'admin123' -S /data/3306/mysql.sock

mysql> select user,host from mysql.user;

+------+--------------------+

| user | host |

+------+--------------------+

| root | 127.0.0.1 |

| root | ::1 |

| | localhost |

| root | localhost |

| | mysqlslave.t4x.org |

| root | mysqlslave.t4x.org |

+------+--------------------+

mysql> delete from mysql.user;

Query OK, 6 rows affected (0.00 sec)

mysql> select * from mysql.user\G

mysql> grant all privileges on *.* to system@'localhost' identified by 'admin123' with grant option;

mysql> flush privileges;

mysql> select * from mysql.user\G

*************************** 1. row ***************************

Host: localhost

User: system

Password: *01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C

mysql> create database da3306t21 default character set utf8 collate utf8_general_ci;

mysql> drop database test;

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| da3306t21 |

| mysql |

| performance_schema |

+--------------------+

mysql> grant replication slave on *.* to byrd@'1.1.1.101' identified by 'passwd123'; #授权从库ip

mysql> select user,host from mysql.user;

+--------+-----------+

| user | host |

+--------+-----------+

| byrd | 1.1.1.101 |

| system | localhost |

+--------+-----------+

mysql> flush tables with read lock; #锁表

mysql> show variables like '%timeout%'; #查询超时时间，默认28800

mysql> show variables like '%timeout%';

+----------------------------+----------+

| Variable_name | Value |

+----------------------------+----------+

| interactive_timeout | 28800 |

| wait_timeout | 28800 |

+----------------------------+----------+

mysql> show master status; #获取bin_log位置点

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 188 | | |

+------------------+----------+--------------+------------------+

[root@mysqlMaster /]# /usr/local/mysql5/bin/mysqldump -usystem -p'admin123' -S /data/3306/mysql.sock --events -B -A | gzip > /tmp/all.sql.gz

mysql> show master status; #查询位置是否偏移（-F 除外）

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 188 | | |

+------------------+----------+--------------+------------------+

[root@mysqlMaster /]# scp -P22 /tmp/all.sql.gz root@1.1.1.101:/tmp

root@1.1.1.101's password:

all.sql.gz 100% 148KB 148.2KB/s 00:00

[root@mysqlMaster /]# egrep "server-id|log" /data/3306/my.cnf

log-bin = /data/3306/data/mysql-bin

server-id = 1

3308从库配置：文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/multi-mysqld-master-slave-synchronization/

[root@mysqlSlave /]# egrep "server-id|log" /data/3308/my.cnf  #需要重新启动mysql
#log-bin = /data/3308/data/mysql-bin     #备份服务器、下面有其他从库【级联同步】，需要开bin_log
server-id = 999
[root@mysqlSlave /]# ll /tmp/
-rw-r--r--. 1 root root 151725 Apr 22 01:34 all.sql.gz
[root@mysqlSlave /]# gzip -d /tmp/all.sql.gz 
[root@mysqlSlave /]# ll /tmp/
-rw-r--r--. 1 root root 552100 Apr 22 01:34 all.sql
[root@mysqlSlave /]# /usr/local/mysql5/bin/mysql -usystem -p'admin123' -S /data/3308/mysql.sock      
ERROR 1045 (28000): Access denied for user 'system'@'localhost' (using password: YES)
[root@mysqlSlave /]# /usr/local/mysql5/bin/mysql -uroot -p'' -S /data/3308/mysql.sock 
mysql> select user,host from mysql.user;
+--------+-----------+
| user   | host      |
+--------+-----------+
| byrd   | 1.1.1.101 |
| system | localhost |
+--------+-----------+

mysql> flush privileges;
[root@mysqlSlave /]# /usr/local/mysql5/bin/mysql -usystem -p'admin123' -S /data/3308/mysql.sock
mysql> CHANGE MASTER TO
    -> MASTER_HOST='1.1.1.22', 
    -> MASTER_PORT=3306,
    -> MASTER_USER='byrd',
    -> MASTER_PASSWORD='passwd123',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=188; 
Query OK, 0 rows affected (0.25 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 1.1.1.22
                  Master_User: byrd
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 188
               Relay_Log_File: mysqlSlave-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0

[root@mysqlSlave /]# egrep "server-id|log" /data/3308/my.cnf #需要重新启动mysql

#log-bin = /data/3308/data/mysql-bin #备份服务器、下面有其他从库【级联同步】，需要开bin_log

server-id = 999

[root@mysqlSlave /]# ll /tmp/

-rw-r--r--. 1 root root 151725 Apr 22 01:34 all.sql.gz

[root@mysqlSlave /]# gzip -d /tmp/all.sql.gz

[root@mysqlSlave /]# ll /tmp/

-rw-r--r--. 1 root root 552100 Apr 22 01:34 all.sql

[root@mysqlSlave /]# /usr/local/mysql5/bin/mysql -usystem -p'admin123' -S /data/3308/mysql.sock

ERROR 1045 (28000): Access denied for user 'system'@'localhost' (using password: YES)

[root@mysqlSlave /]# /usr/local/mysql5/bin/mysql -uroot -p'' -S /data/3308/mysql.sock

mysql> select user,host from mysql.user;

+--------+-----------+

| user | host |

+--------+-----------+

| byrd | 1.1.1.101 |

| system | localhost |

+--------+-----------+

mysql> flush privileges;

[root@mysqlSlave /]# /usr/local/mysql5/bin/mysql -usystem -p'admin123' -S /data/3308/mysql.sock

mysql> CHANGE MASTER TO

-> MASTER_HOST='1.1.1.22',

-> MASTER_PORT=3306,

-> MASTER_USER='byrd',

-> MASTER_PASSWORD='passwd123',

-> MASTER_LOG_FILE='mysql-bin.000001',

-> MASTER_LOG_POS=188;

Query OK, 0 rows affected (0.25 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 1.1.1.22

Master_User: byrd

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 188

Relay_Log_File: mysqlSlave-relay-bin.000002

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/multi-mysqld-master-slave-synchronization/

参考文档：文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/multi-mysqld-master-slave-synchronization/

多实例配置：https://note.t4x.org/database/mysql-instance-configuration/
主从同步：https://note.t4x.org/database/configuration-mysql-master-slave/文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/multi-mysqld-master-slave-synchronization/ 文章源自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/multi-mysqld-master-slave-synchronization/

申明：除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!

On this day in past years

May

Hot search

On this day in past years

Comment