MySQL单机多实例主从复制基础实践

  • 2019-09-02
  • 0
  • 0
约定:
* 主库端口为3307
* 从库端口为3308和3309

1、创建多个目录

[root@mysql50 ~]# mkdir -p /data/330{7,8,9}

2、准备多套配置文件

# 只在主库上开启binlog日志
[root@mysql50 ~]# cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF
[root@mysql50 ~]# cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
EOF
[root@mysql50 ~]# cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
EOF

3、初始化多套数据

[root@mysql50 ~]# cd /usr/local/mysql/scripts
[root@mysql50 scripts]# ./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data
[root@mysql50 scripts]# ./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data
[root@mysql50 scripts]# ./mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/3309/data
# 初始化完成的目录结构
[root@mysql50 scripts]# ll /data/3307/data/
total 110600
-rw-rw---- 1 mysql mysql 12582912 Jul 10 10:00 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Jul 10 10:00 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Jul 10 10:00 ib_logfile1
drwx------ 2 mysql mysql     4096 Jul 10 10:00 mysql
drwx------ 2 mysql mysql     4096 Jul 10 10:00 performance_schema
drwx------ 2 mysql mysql        6 Jul 10 10:00 test

授权目录

[root@mysql50 ~]# chown -R mysql.mysql /data/*

5、利用systemd管理多实例

[root@mysql50 ~]# cd /usr/lib/systemd/system
[root@mysql50 system]# vim mysqld3307.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
[root@mysql50 system]# vim mysqld3308.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
[root@mysql50 system]# vim mysqld3309.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
# 重载system
[root@mysql50 system]# systemctl daemon-reload

6、启动多实例

[root@mysql50 system]# systemctl start mysqld3307.service 
[root@mysql50 system]# systemctl start mysqld3308.service  
[root@mysql50 system]# systemctl start mysqld3309.service  
## 验证
[root@mysql50 system]# ss -lnp |grep 330  
u_str  LISTEN     0      80     /data/3308/mysql.sock 47028                 * 0                   users:(("mysqld",pid=8032,fd=12))
u_str  LISTEN     0      128    /run/dbus/system_bus_socket 33090                 * 0                   users:(("dbus-daemon",pid=5996,fd=3),("systemd",pid=1,fd=41))
u_str  LISTEN     0      128    /var/run/rpcbind.sock 33093                 * 0                   users:(("rpcbind",pid=5997,fd=3),("systemd",pid=1,fd=70))
u_str  LISTEN     0      80     /data/3309/mysql.sock 47050                 * 0                   users:(("mysqld",pid=8039,fd=12))
u_str  LISTEN     0      80     /data/3307/mysql.sock 46673                 * 0                   users:(("mysqld",pid=8002,fd=14))
tcp    LISTEN     0      80       :::3306                 :::*                   users:(("mysqld",pid=6796,fd=10))
tcp    LISTEN     0      80       :::3307                 :::*                   users:(("mysqld",pid=8002,fd=11))
tcp    LISTEN     0      80       :::3308                 :::*                   users:(("mysqld",pid=8032,fd=10))
tcp    LISTEN     0      80       :::3309                 :::*                   users:(("mysqld",pid=8039,fd=10))
[root@mysql50 system]# mysql -S /data/3307/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
[root@mysql50 system]# mysql -S /data/3308/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
[root@mysql50 system]# mysql -S /data/3309/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+

7、连接到主库进行配置

[root@mysql50 system]# mysql -S /data/3307/mysql.sock
# 查看server_id以及log_bin是否开启
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 7     |
+---------------+-------+
1 row in set (0.01 sec)
# 创建一个用户从库复制的账号ljc
mysql> grant replication slave on *.* to ljc@'10.0.0.%' identified by '123';
Query OK, 0 rows affected (0.01 sec)

# replication slave为MySQL同步的必须权限
# *.*表示所有库所有表
# ljc@'10.0.0.%' ljc为同步账号,10.0.0.%为授权主机网段
# identified by '123' 授权密码
# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
# 可以查看是否创建成功
mysql> select user,host from mysql.user where user='ljc';
+------+----------+
| user | host     |
+------+----------+
| ljc  | 10.0.0.% |
+------+----------+
1 row in set (0.00 sec)

mysql> show grants for ljc@'10.0.0.%';
+-----------------------------------------------------------------------------------------------------------------------+
| Grants for ljc@10.0.0.%                                                                                               |
+-----------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'ljc'@'10.0.0.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 锁表,实现对主数据库只读,防止配置过程中数据的变化
mysql> flush table with read lock; 
Query OK, 0 rows affected (0.01 sec)
# 查看主库的状态,查看binlog日志文件名和二进制binlog日志偏移量
# 之后不再对主库进行任何操作
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      199 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 也可这么看文件名和偏移量(在导出备份数据库之后)
[root@mysql50 ~]# sed -n '22p' /tmp/all.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=199;

8、备份主库恢复到从库

# 导出数据库
[root@mysql50 ~]#  mysqldump -S /data/3307/mysql.sock -A --master-data=2 --single-transaction  -R --triggers >/tmp/all.sql
#对主库解锁
mysql> unlock tables;
# 登录3308和3309的库,恢复
[root@mysql50 ~]# mysql -S /data/3308/mysql.sock
mysql> source /tmp/all.sql

9、登录从库,配置主库同步参数

mysql> CHANGE MASTER TO
    -> MASTER_HOST='10.0.0.50',
    -> MASTER_USER='ljc',
    -> MASTER_PASSWORD='123',
    -> MASTER_PORT=3307,
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=199;

## 默认配置信息可以这么查看:
mysql> help change master to
# 开启主从(开启IO和SQL线程):
mysql> start slave;
# 查看主从状态:
show slave status\G
# 两个YES
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

10、测试

#登录主库
[root@mysql50 ~]# mysql -uroot -S /data/3307/mysql.sock 
#创建一个测试库
mysql> create database test1;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test1              |
+--------------------+
5 rows in set (0.00 sec)
# 登录从库
[root@mysql50 ~]# mysql -S /data/3308/mysql.sock 
# 查看是否同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test1              |
+--------------------+
5 rows in set (0.00 sec)

11、主从失败情况说明

如果IO线程是NO

# 1.检测网络:
[root@mysql50 ~]# ping 127.0.0.1

# 2.检测端口:
[root@mysql50 ~]# telnet 127.0.0.1 3307

# 3.检测账号,密码:
mysql -uljc -p123 -h127.0.0.1 -P 3307

评论

还没有任何评论,你来说两句吧

提供支持 - 友情链接 - 衫小寨