标签：multi -- 主从复制 leo mysql5718 Master mysql mysqld root
文档课题：mysqld_multi实现单机主从复制.
数据库：mysql 5.7.18
系统：rhel 7.3
安装包：mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz
1、理论知识
mysqld_multi用于在一台服务器上管理多个mysqld进程，这些进程使用不同的socket文件并监听不同端口.mysqld_multi可以批量启动、关闭、或报告mysqld进程状态.
2、开始安装
2.1、创建目录
--此处将mysql安装在/usr/local/mysql目录
[root@leo-mysql5718 ~]# mkdir -p /usr/local/mysql
--创建data目录
[root@leo-mysql5718 ~]# mkdir -p /usr/local/mysql/data{3306,3307,3308}
2.2、安装包处理
--解压安装包.
[root@leo-mysql5718 ~]# tar -xvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz
[root@leo-mysql5718 ~]# ll
total 639104
-rw-------. 1 root root 2248 May 23 16:11 anaconda-ks.cfg
-rw-r--r--. 1 root root 2341 May 23 16:26 initial-setup-ks.cfg
drwxr-xr-x. 9 root root 129 May 24 12:06 mysql-5.7.18-linux-glibc2.5-x86_64
-rw-r--r--. 1 root root 654430368 May 24 11:38 mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz
-rw-r--r--. 1 root root 0 May 24 11:35 test.txt
[root@leo-mysql5718 ~]# mv mysql-5.7.18-linux-glibc2.5-x86_64/* /usr/local/mysql/
2.3、创建用户
--创建mysql用户组及用户.
[root@leo-mysql5718 ~]# groupadd mysql
[root@leo-mysql5718 ~]# useradd -r -g mysql mysql
--修改权限
[root@leo-mysql5718 ~]# chown -R mysql:mysql /usr/local/mysql
2.4、初始化实例
初始化实例，mysql_install_db已deprecated，现使用mysqld的initialize来进行初始化.
[root@leo-mysql5718 bin]# pwd
/usr/local/mysql/bin
[root@leo-mysql5718 bin]# ./mysqld --no-defaults --initialize-insecure --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data3306 --user=mysql --explicit_defaults_for_timestamp
2023-05-24T06:27:09.165261Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-05-24T06:27:09.223712Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-05-24T06:27:09.306491Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 02d6e01a-f9fc-11ed-bf85-000c29fbcf67.
2023-05-24T06:27:09.308347Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-05-24T06:27:09.309056Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@leo-mysql5718 bin]# ./mysqld --no-defaults --initialize-insecure --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data3307 --user=mysql --explicit_defaults_for_timestamp
2023-05-24T06:28:54.210253Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-05-24T06:28:54.271066Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-05-24T06:28:54.285185Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 41695abf-f9fc-11ed-8297-000c29fbcf67.
2023-05-24T06:28:54.286185Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-05-24T06:28:54.287174Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
[root@leo-mysql5718 bin]# ./mysqld --no-defaults --initialize-insecure --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data3308 --user=mysql --explicit_defaults_for_timestamp
2023-05-24T06:29:04.023119Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-05-24T06:29:04.068276Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-05-24T06:29:04.141918Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 47495f61-f9fc-11ed-842e-000c29fbcf67.
2023-05-24T06:29:04.143754Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-05-24T06:29:04.144363Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
参数说明：
--no-defaults：不读取默认的/etc/my.cnf全局配置文件，防止冲突问题
--initialize-insecure：初始化且不需要生成密码
--basedir：mysql安装目录
--datadir：数据目录
--user：生成的文件用户为mysql
--explicit_defaults_for_timestamp：timestamp已deprecated
2.5、环境变量
将mysql命令加入环境变量
[root@leo-mysql5718 ~]# vi /etc/profile
添加如下：
MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin
--使环境变量生效.
[root@leo-mysql5718 ~]# source /etc/profile
2.6、创建my.cnf
[root@leo-mysql5718 etc]# cp my.cnf my_bak.cnf
[root@leo-mysql5718 etc]# echo '' > my.cnf
[root@leo-mysql5718 etc]# vi my.cnf
添加如下：
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe #mysqld命令位置，用于启动mysql实例，也可以指定为mysqld_safe命令的位置
mysqladmin=/usr/local/mysql/bin/mysqladmin #用于停止mysql实例
user=root #调用mysqladmin时使用的账号
#password=123456 #调用mysqladmin时使用的账号密码，因为使用了initialize-insecure，所以root用户最开始是没有密码的
log=/usr/local/mysql/mysql_multi.log
[mysqld3306]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data3306
port=3306
user=mysql
socket=/tmp/mysql.sock3306
server_id=1
log_bin=mysql-bin #主库
[mysqld3307]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data3307
port=3307
user=mysql
socket=/tmp/mysql.sock3307
server_id=2
[mysqld3308]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data3308
port=3308
user=mysql
socket=/tmp/mysql.sock3308
server_id=3
参数说明：
mysqld_multi中配置的user和password是mysql的用户名和密码.为使用mysqld_multi命令，每个mysql实例都必须要提供一个相同的用户名和密码，用于启动和停止服务，而且要确保该用户具有停止mysql实例的权限.在[mysqlN]中配置的user是linux中的用户，是启动mysql服务实例时使用的用户，mysqld_multi提供start、stop、reload、restart和report等命令，具体可参见官方文档https://dev.mysql.com/doc/refman/5.7/en/mysqld-multi.html.
2.7、启动实例
--启动mysql实例.
[root@leo-mysql5718 etc]# mysqld_multi start
--查看实例运行状态.
[root@leo-mysql5718 etc]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
--通过netstat -tln查看.
[root@leo-mysql5718 etc]# netstat -tln | grep 330*
tcp6 0 0 :::3306 :::* LISTEN
tcp6 0 0 :::3307 :::* LISTEN
tcp6 0 0 :::3308 :::* LISTEN
2.8、配置主从关系
2.8.1、登录主从数据库
--分别打开3个命令窗口，执行以下命令登录主从数据库.
[root@leo-mysql5718 ~]# mysql -h127.0.0.1 -uroot -P3306 -p
[root@leo-mysql5718 ~]# mysql -h127.0.0.1 -uroot -P3307 -p
[root@leo-mysql5718 ~]# mysql -h127.0.0.1 -uroot -P3308 -p
说明：因初始化数据库时，没有为root用户生成密码，因此当出现提示输入password时，直接按enter键.
2.8.2、建复制账号
--在3个命令行窗口都执行以下命令，用于创建复制账号.
mysql> grant replication slave,replication client on *.* to slave@'localhost' identified by 'slave';
说明：理论上在主库创建复制账户即可，但考虑到主备切换，因此在从库也创建复制账号，另外主从复制，本身只需要replication slave权限即可，replication client权限是让用户拥有执行show master status和show slave status的权限.
2.8.3、配置主从同步
在3307和3308从库执行以下命令实现主从同步，目的在于告诉从库连接主库的方式并重复binlog日志.旧版本mysql是通过修改my.cnf来配置，新版本中使用change master to.该语句完全替代my.cnf中相应的设置，并允许指向别的主库时无须重启备库，如下为基本命令：
mysql> change master to \
master_host='localhost', \
master_user='slave', \
master_port=3306, \
master_password='slave', \
master_log_file='mysql-bin.000001', \
master_log_pos=0;
说明：只需配置一次即可，此后重启slave会自动连接到master同步数据，需要注意的是此时复制并未真正开始.
2.8.4、同步前状态
--在主库3306执行以下命令，查看主库状态，如下为主从启动同步前主库状态.
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 466 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------+------+---------+------+----------+------------------+
| 5 | root | localhost:35374 | NULL | Query | 0 | starting | show processlist |
+----+------+-----------------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
--从库3307或3308执行以下命令.
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: localhost
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: leo-mysql5718-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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: 4
Relay_Log_Space: 154
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: NULL
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: 0
Master_UUID:
Master_Info_File: /usr/local/mysql/data3307/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
说明：slave_io_state、slave_io_running、slave_sql_running三列显示从库复制尚未开始，Seconds_Behind_Master为判断slave落后master的重要依据.
2.8.5、开启复制
--此时在从库3307和3308上分别执行以下命令，开始复制.
mysql> start slave;
--在3307和3308再次执行show slave status查看从库状态.
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event #等待主库发送bin-log同步事件
Master_Host: localhost
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 466
Relay_Log_File: leo-mysql5718-relay-bin.000002
Relay_Log_Pos: 679
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes #状态变为YES
Slave_SQL_Running: Yes #状态变为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: 466
Relay_Log_Space: 894
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: 1
Master_UUID: 02d6e01a-f9fc-11ed-bf85-000c29fbcf67
Master_Info_File: /usr/local/mysql/data3307/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
--此时再查看主库3306状态.
mysql> show processlist \G
*************************** 1. row ***************************
Id: 5
User: root
Host: localhost:35374
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
*************************** 2. row ***************************
Id: 6
User: slave
Host: localhost:54838
db: NULL
Command: Binlog Dump
Time: 192
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
*************************** 3. row ***************************
Id: 7
User: slave
Host: localhost:54840
db: NULL
Command: Binlog Dump
Time: 185
State: Master has sent all binlog to slave; waiting for more updates
Info: NULL
3 rows in set (0.00 sec)
说明：可以看到2个线程执行了binlog dump命令，表明两个从库已经开始复制.
3、数据测试
3.1、建测试数据
现在主库3306上创建测试库booksDB，然后创建表books并插入记录.测试数据是否会同步到两个从库.
mysql> create database booksDB;
Query OK, 1 row affected (0.00 sec)
mysql> use booksDB;
Database changed
mysql> create table books
-> (
-> bk_id int not null primary key,
-> bk_title varchar(50) not null,
-> copyright year not null
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into books values
-> (11078,'Learning MYSQL',2010),
-> (11033,'Study Html',2011),
-> (11035,'How to use php',2003),
-> (11072,'Teach yourself javascript',2005),
-> (11028,'Learning C++',2005),
-> (11069,'MYSQL professional',2009),
-> (11026,'Guide to MySQL 5.7',2008),
-> (11041,'Inside VC++',2011);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
3.2、验证数据
分别在从库3307和3308执行以下命令，查看数据是否已经同步.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| booksDB |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use booksDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_booksDB |
+-------------------+
| books |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from books;
+-------+---------------------------+-----------+
| bk_id | bk_title | copyright |
+-------+---------------------------+-----------+
| 11026 | Guide to MySQL 5.7 | 2008 |
| 11028 | Learning C++ | 2005 |
| 11033 | Study Html | 2011 |
| 11035 | How to use php | 2003 |
| 11041 | Inside VC++ | 2011 |
| 11069 | MYSQL professional | 2009 |
| 11072 | Teach yourself javascript | 2005 |
| 11078 | Learning MYSQL | 2010 |
+-------+---------------------------+-----------+
8 rows in set (0.00 sec)
说明：如上所示，主节点数据成功同步到从节点.
参考网址：https://blog.51cto.com/u_15553139/5179643
标签：multi,
--,
主从复制,
leo,
mysql5718,
Master,
mysql,
mysqld,
root
From： https://blog.51cto.com/u_12991611/6342722