MySQL主从:
例如主库id为: 3, 从库ip为: 192.168.1.2 依次运行以下脚本:
- 自定义脚本: bin/utils 目录下
1). 配置主库: 在主库上运行 mysql-repl-master-1.bash
如: ./mysql-repl-master-1.bash 3 192.168.1.2
2). 配置从库: 在从库上运行脚本 ./mysql-repl-slave-2.bash
如:
从库id: 2,
主库id: 3 主库ip: 192.168.1.3
first.sql目录: /root/first.sql
second.sql目录 /root/second.sql
主库log_file名称: master_3.000003 ( 在主库mysql运行: show master status 查看 )
主库log_pos值: 2389 ( 在主库mysql运行: show master status 查看 )
./mysql-repl-slave-2.bash 2 3 192.168.1.3 /root/first.sql /root/second.sql master_3.000003 2389
3). unlock 主库表: 在主库上运行:
./mysql-repl-master-3.bash
1. 啥是MySQL主从:
即两台机器MySQL数据同步
. MySQL主从又叫Replication, AB复制.
简单来说就是A和B两台机器做主从后, 在A上写数据, 另外一台B也会跟着写数据, 两者数据实时同步.
. MySQL主从是基于binlog的, 主上开启binlog才能进行主从.
主从过程大致有3个步骤:
1). 主将更改操作记录到binlog里
2). 从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里
3). 从根据relaylog里面的sql语句按顺序执行
. 主上有一个log dump线程, 用来和从的I/O线程传递binlog
. 从上有两个线程, 其中I/O线程用来同步主的binlog并生成relaylog, 另外一个SQL线程用来把relaylog里面的sql语句落地(执行)
主: binlog
从: relaylog
主从的应用场景:
(1)做数据的备份, (主: 作读写数据, 从: 实时同步, 当主宕机时, 从也可以即时提供服务 )
(2)也是做备份. (客户在从这台机器上读取数据(但是不能再从上写), 减轻主的压力 )
准备工作
. 两台机器都装上mysql, 并且都开启mysql的服务
1.配置主:
主从配置-主上操作
.安装mysql
.修改my.cnf, 增加server-id=133和log_bin=master1
.修改完配置文件后, 启动或者重启mysqld服务
. 创建用作同步数据的用户:
主: 192.168.1.3
从: 192.168.1.2
- 修改 my.cnf, 增加 server-id=3 和 log-bin=master_3
- 修改完配置文件后, 启动或者重启mysqld服务
重启完后, 我们可以发现 /var/lib/mysql/下生成了以在配置文件中定义 log-bin=master_3 为开头的文件.
这些文件很重要, 不然不能实现主从
修改 my.cnf, 加入以下内容:
#refers to https://www.cnblogs.com/kevingrace/p/6256603.html
server-id=3
log-bin=master_3
binlog-do-db=first
binlog-do-db=second
binlog-ignore-db=mysql
sync_binlog=5
binlog_format=mixed
然后:
service mysqld restart
[root@localhost etc]# cd /var/lib/mysql/
[root@localhost mysql]# ls
aria_log.00000001 first ib_logfile1 master_3.index mysql_upgrade_info
aria_log_control ib_buffer_pool ibtmp1 mysql performance_schema
auto.cnf ibdata1 master_3.000001 mysql.sock sys
second ib_logfile0 master_3.000002 mysql.sock.lock
[root@localhost mysql]#
[root@localhost mysql]# ls -tl | grep -i master
-rw-r-----. 1 mysql mysql 154 Mar 19 01:27 master_3.000002
-rw-r-----. 1 mysql mysql 40 Mar 19 01:27 master_3.index
-rw-r-----. 1 mysql mysql 177 Mar 19 01:27 master_3.000001
[root@localhost mysql]#
2. 备份mysql库, 导出主库:
mysqldump -uroot second -p > ~/second.sql
mysqldump -uroot first -p > ~/first.sql
3. 创建用作同步数据的用户
1). 进入到mysql
mysql -uroot -p
2). 创建用户:
grant replication slave on *.* to 'repl'@192.168.1.2 identified by '!!abc23Jordan!!';
4. 锁定表:
#(为了同步前数据一致), 用 unlock tables 解除锁定.
flush tables with read lock;
#(查看一下position 和 file )
show master status;
#设置数据同步权限
flush privileges;
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master_3.000002 | 449 | first,second | mysql | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for repl@192.168.1.2;
+----------------------------------------------------------+
| Grants for repl@192.168.1.2 |
+----------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.2' |
+----------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
6. 查看 /var/lib/mysql/:
[root@localhost mysql]# pwd
/var/lib/mysql
[root@localhost mysql]# ls
aria_log.00000001 first ib_logfile1 master_3.index mysql_upgrade_info
aria_log_control ib_buffer_pool ibtmp1 mysql performance_schema
auto.cnf ibdata1 master_3.000001 mysql.sock sys
second ib_logfile0 master_3.000002 mysql.sock.lock
[root@localhost mysql]# ll | grep -i mysql
-rw-rw----. 1 mysql mysql 16384 Jan 17 22:27 aria_log.00000001
-rw-rw----. 1 mysql mysql 52 Jan 17 22:27 aria_log_control
-rw-r-----. 1 mysql mysql 56 Jan 18 04:00 auto.cnf
drwxr-x---. 2 mysql mysql 4096 Mar 18 23:07 second
drwxr-x---. 2 mysql mysql 4096 Mar 18 23:06 first
-rw-r-----. 1 mysql mysql 543 Mar 19 01:27 ib_buffer_pool
-rw-rw----. 1 mysql mysql 18874368 Mar 19 01:27 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Mar 19 01:27 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jan 18 04:00 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Mar 19 01:27 ibtmp1
-rw-r-----. 1 mysql mysql 177 Mar 19 01:27 master_3.000001
-rw-r-----. 1 mysql mysql 601 Mar 19 01:50 master_3.000002
-rw-r-----. 1 mysql mysql 40 Mar 19 01:27 master_3.index
drwx------. 2 mysql mysql 4096 Jan 18 04:07 mysql
srwxrwxrwx. 1 mysql mysql 0 Mar 19 01:27 mysql.sock
-rw-------. 1 mysql mysql 5 Mar 19 01:27 mysql.sock.lock
-rw-r--r--. 1 root root 6 Jan 18 04:07 mysql_upgrade_info
drwxr-x---. 2 mysql mysql 8192 Jan 18 04:07 performance_schema
drwxr-x---. 2 mysql mysql 8192 Jan 18 04:07 sys
[root@localhost mysql]# ll | grep -i master_3.
-rw-r-----. 1 mysql mysql 177 Mar 19 01:27 master_3.000001
-rw-r-----. 1 mysql mysql 601 Mar 19 01:50 master_3.000002
-rw-r-----. 1 mysql mysql 40 Mar 19 01:27 master_3.index
[root@localhost mysql]#
7. 备份一下要同步的库
mysqldump -uroot -p123456 first > /tmp/first.sql
mysqldump -uroot -p123456 second > /tmp/second.sql
8. 复制 要备份的数据库到 从库机器上:
[root@localhost mysql]# scp ~/bg*.sql root@192.168.1.2:~/
The authenticity of host '192.168.1.2 (192.168.1.2)' can't be established.
ECDSA key fingerprint is SHA256:4PXh3N2rwjcM3fxPSN1odIyZANh1vN3MJrVo4W37S6g.
ECDSA key fingerprint is MD5:a8:78:22:5c:86:9e:62:d6:12:d4:96:05:e2:c9:47:78.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.2' (ECDSA) to the list of known hosts.
root@192.168.1.2's password:
second.sql 100% 16KB 3.8MB/s 00:00
first.sql 100% 67KB 8.4MB/s 00:00
[root@localhost mysql]#
配置从库
-
查看my.cnf, 配置server-id=2, 必须和主库不一样.
-
修改my.cnf, 加入:
#replication
server-id=2
log-bin=master_3
replicate-do-db=first
replicate-do-db=second
replicate-ignore-db=mysql
3. 重启从库:
[root@localhost etc]# service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[root@localhost etc]#
[root@localhost etc]# ping 192.168.1.3
PING 192.168.1.3 (192.168.1.3) 56(84) bytes of data.
64 bytes from 192.168.1.3: icmp_seq=1 ttl=64 time=1.17 ms
^C
--- 192.168.1.3 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 1.173/1.173/1.173/0.000 ms
[root@localhost etc]#
[root@localhost etc]#
[root@localhost etc]# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2019-03-19 01:57:52 EDT; 1min 32s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 99901 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 99880 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 99904 (mysqld)
Tasks: 27
CGroup: /system.slice/mysqld.service
└─99904 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Mar 19 01:57:47 localhost.localdomain systemd[1]: Starting MySQL Server...
Mar 19 01:57:52 localhost.localdomain systemd[1]: Started MySQL Server.
[root@localhost etc]#
4. 进入从mysql, 建立数据库, 并导入数据:
1). 导入 second:
[root@localhost etc]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.25 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database first character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)
mysql> create database second character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> use second;
Database changed
mysql> source /root/second.sql;
mysql> show tables;
+---------------------------+
| Tables_in_second |
+---------------------------+
| info |
+---------------------------+
12 rows in set (0.01 sec)
mysql>
2). 导入 first:
[root@localhost etc]# mysql -uroot first -p < /root/first.sql
Enter password:
[root@localhost etc]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.25 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use first;
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_first |
+----------------------------------+
| arena |
+----------------------------------+
49 rows in set (0.01 sec)
mysql>
5. 配置主从同步指令:
1). 执行同步前, 先关闭slave
stop slave;
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
2). 修改主库属性: master_log_file 和 master_log_pos 可在主库中用: show master status来看:
mysql> change master to master_host='192.168.1.3',master_user='repl',master_password='!!abc23Jordan!!',master_log_file='master_3.000002',master_log_pos=1397;
Query OK, 0 rows affected, 2 warnings (0.05 sec)
3). 开启 slave:
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
4). 查看同步状态:
当 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes 表明 主从已经实现同步了!
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.3
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master_3.000002
Read_Master_Log_Pos: 1397
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: master_3.000002
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: 1397
Relay_Log_Space: 532
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: 3
Master_UUID: 71cb20a4-1aff-11e9-9ea6-000c2942f951
Master_Info_File: /var/lib/mysql/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)
ERROR:
No query specified
mysql>
6. 解除主库的锁定:
unlock tables;
7. 测试主从同步的效果
1). 在主库更新数据
mysql> use baac;
mysql> update info set ip='192.168.1.233';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select ip from info;
+---------------+
| ip |
+---------------+
| 192.168.1.253 |
+---------------+
1 row in set (0.00 sec)
mysql> select ip from info;
+---------------+
| ip |
+---------------+
| 192.168.1.233 |
+---------------+
1 row in set (0.00 sec)
mysql>
2). 在从库 2上 查看更新的数据:
mysql> select ip from info;
+---------------+
| ip |
+---------------+
| 192.168.1.233 |
+---------------+
1 row in set (0.00 sec)
mysql>
8. 不成功的因素:
1). 防火墙: service firewalld stop
2). selinux:
3). 用户名,
4). 其它配置问题
9. 其它信息:
1). 从库目录:
[root@localhost etc]# ls -tl /var/lib/mysql
total 188528
-rw-r-----. 1 mysql mysql 136 Mar 19 03:00 master.info
-rw-r-----. 1 mysql mysql 12582912 Mar 19 03:00 ibtmp1
-rw-r-----. 1 mysql mysql 50331648 Mar 19 02:59 ib_logfile0
-rw-r-----. 1 mysql mysql 79691776 Mar 19 02:59 ibdata1
-rw-r-----. 1 mysql mysql 65 Mar 19 02:59 relay-log.info
-rw-r-----. 1 mysql mysql 902 Mar 19 02:59 localhost-relay-bin.000004
-rw-r-----. 1 mysql mysql 58 Mar 19 02:50 localhost-relay-bin.index
-rw-r-----. 1 mysql mysql 211 Mar 19 02:50 localhost-relay-bin.000003
srwxrwxrwx. 1 mysql mysql 0 Mar 19 02:50 mysql.sock
-rw-------. 1 mysql mysql 7 Mar 19 02:50 mysql.sock.lock
-rw-r-----. 1 mysql mysql 154 Mar 19 02:50 master_3.000001
-rw-r-----. 1 mysql mysql 20 Mar 19 02:50 master_3.index
-rw-r-----. 1 mysql mysql 728 Mar 19 02:50 ib_buffer_pool
drwxr-x---. 2 mysql mysql 4096 Mar 19 02:22 first
drwxr-x---. 2 mysql mysql 4096 Mar 19 02:19 second
-rw-r--r--. 1 mysql mysql 451 Mar 18 05:05 public_key.pem
-rw-------. 1 mysql mysql 1675 Mar 18 05:05 private_key.pem
-rw-r--r--. 1 mysql mysql 1107 Mar 18 05:05 client-cert.pem
-rw-------. 1 mysql mysql 1675 Mar 18 05:05 client-key.pem
-rw-r--r--. 1 mysql mysql 1107 Mar 18 05:05 server-cert.pem
-rw-------. 1 mysql mysql 1679 Mar 18 05:05 server-key.pem
-rw-r--r--. 1 mysql mysql 1107 Mar 18 05:05 ca.pem
-rw-------. 1 mysql mysql 1679 Mar 18 05:05 ca-key.pem
drwxr-x---. 2 mysql mysql 8192 Mar 18 05:05 sys
drwxr-x---. 2 mysql mysql 4096 Mar 18 05:05 mysql
drwxr-x---. 2 mysql mysql 8192 Mar 18 05:05 performance_schema
-rw-r-----. 1 mysql mysql 56 Mar 18 05:05 auto.cnf
-rw-r-----. 1 mysql mysql 50331648 Mar 18 05:05 ib_logfile1
[root@localhost etc]#
2). 主库目录:
[root@localhost mysql]# ls -tl /var/lib/mysql
total 129112
-rw-r-----. 1 mysql mysql 50331648 Mar 19 02:59 ib_logfile0
-rw-rw----. 1 mysql mysql 18874368 Mar 19 02:59 ibdata1
-rw-r-----. 1 mysql mysql 1978 Mar 19 02:59 master_3.000002
-rw-r-----. 1 mysql mysql 12582912 Mar 19 02:10 ibtmp1
srwxrwxrwx. 1 mysql mysql 0 Mar 19 01:27 mysql.sock
-rw-------. 1 mysql mysql 5 Mar 19 01:27 mysql.sock.lock
-rw-r-----. 1 mysql mysql 40 Mar 19 01:27 master_3.index
-rw-r-----. 1 mysql mysql 543 Mar 19 01:27 ib_buffer_pool
-rw-r-----. 1 mysql mysql 177 Mar 19 01:27 master_3.000001
drwxr-x---. 2 mysql mysql 4096 Mar 18 23:07 second
drwxr-x---. 2 mysql mysql 4096 Mar 18 23:06 first
-rw-r--r--. 1 root root 6 Jan 18 04:07 mysql_upgrade_info
drwxr-x---. 2 mysql mysql 8192 Jan 18 04:07 sys
drwx------. 2 mysql mysql 4096 Jan 18 04:07 mysql
drwxr-x---. 2 mysql mysql 8192 Jan 18 04:07 performance_schema
-rw-r-----. 1 mysql mysql 56 Jan 18 04:00 auto.cnf
-rw-r-----. 1 mysql mysql 50331648 Jan 18 04:00 ib_logfile1
-rw-rw----. 1 mysql mysql 16384 Jan 17 22:27 aria_log.00000001
-rw-rw----. 1 mysql mysql 52 Jan 17 22:27 aria_log_control
[root@localhost mysql]#
网友评论