master 通过 bindump ,send binlog updata 到slave
mysql-bin000000001 #主库的二进制日志
slave通过change master to 对master进行认证
I/O进程读取master的二进制文件，进行保存(IO -> relaylog -> disk持久化)，并且等待master更新
slave sql线程会读取relaylog把数据做一个回放，达到数据更新
一般主从复制会对slave设置read-only，如果修改的话会造成主从不一致
GTID #全局识别ID
GTID_NEXT #标记修改到了哪里
一主多从，不同slave的延迟不同，如果master挂了，会选一个最接近master的接管
主从复制因为有网络延迟所以不可能实时一致性
server1:
mysql> show processlist;
+----+-----------------+---------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+---------------+------+-------------+------+---------------------------------------------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 3400 | Waiting on empty queue | NULL |
| 8 | repl | server2:44946 | NULL | Binlog Dump | 3345 | Master has sent all binlog to slave; waiting for more updates | NULL |
| 9 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+-----------------+---------------+------+-------------+------+---------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)
server2:
mysql> show variables like 'log_slave%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| log_slave_updates | ON |
+-------------------+-------+
1 row in set (0.01 sec)
server1:
mysql> show variables like 'gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| gtid_executed | |
| gtid_executed_compression_period | 1000 |
| gtid_mode | OFF |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
+----------------------------------+-----------+
6 rows in set (0.00 sec)
[root@server1 ~]# /etc/init.d/mysqld stop
Shutting down MySQL.......... SUCCESS!
[root@server1 ~]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/lnmp/mysql
datadir=/data/mysql
socket=/data/mysql/mysql.sock
#skip-grant-tables
server-id=1
log-bin=mysql-bin
gtid_mode=ON
enforce-gtid-consistency=ON
#
[root@server1 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
server1:
mysql> show variables like 'gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| gtid_executed | |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
+----------------------------------+-----------+
6 rows in set (0.01 sec)
[root@server2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!
[root@server2 ~]# vim /etc/my.cnf
gtid_mode=ON
enforce-gtid-consistency=ON
[root@server2 ~]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
server2:
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to master_host='172.25.4.1',master_user='repl',master_password='Westos+123',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.25.4.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 156
Relay_Log_File: server2-relay-bin.000002
Relay_Log_Pos: 371
Relay_Master_Log_File: mysql-bin.000004
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: 156
Relay_Log_Space: 582
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: cc3d2885-dec1-11ea-8aad-5254005a95fe
Master_Info_File: mysql.slave_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: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 1
Network_Namespace:
1 row in set (0.00 sec)
ERROR:
No query specified
master并不会关心slave有没有真正复制过去 #异步，快，但是无法保证一致性
===========================
半同步
master通过IO把二进制文件发送过去之后，必须要slave有一个人ack回应，确定他同步成功
无损复制
server1:
mysql> show variables like 'rpl%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| rpl_read_size | 8192 |
| rpl_stop_slave_timeout | 31536000 |
+------------------------+----------+
2 rows in set (0.00 sec)
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected (0.03 sec)
server2:
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected (0.01 sec)
server1:
mysql> show variables like 'rpl%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_read_size | 8192 |
| rpl_semi_sync_master_enabled | OFF |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
8 rows in set (0.00 sec)
server2:
mysql> show variables like 'rpl%';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| rpl_read_size | 8192 |
| rpl_semi_sync_slave_enabled | OFF |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
4 rows in set (0.01 sec)
server1:
mysql> set global rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'rpl%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_read_size | 8192 |
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
8 rows in set (0.00 sec)
server2:
mysql> set global rpl_semi_sync_slave_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'rpl%';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| rpl_read_size | 8192 |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
4 rows in set (0.00 sec)
server1:
mysql> show status like 'rpl%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)
server2:
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
server1:
mysql> show status like 'rpl%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kaixin |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
mysql> use kaixin
Database changed
mysql> show tables;
+------------------+
| Tables_in_kaixin |
+------------------+
| user_tb |
+------------------+
1 row in set (0.00 sec)
mysql> select * from user_tb;
+-------+----------+
| name | password |
+-------+----------+
| user1 | 123 |
+-------+----------+
1 row in set (0.00 sec)
mysql> insert into user_tb values ('user2',222);
Query OK, 1 row affected (0.01 sec)
mysql> insert into user_tb values ('user3',333);
Query OK, 1 row affected (0.01 sec)
server2:
mysql> show variables like 'rpl%';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| rpl_read_size | 8192 |
| rpl_semi_sync_slave_enabled | ON |
| rpl_semi_sync_slave_trace_level | 32 |
| rpl_stop_slave_timeout | 31536000 |
+---------------------------------+----------+
4 rows in set (0.00 sec)
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.01 sec)
server1:
mysql> insert into user_tb values ('user4',444); #等了server2 10秒
Query OK, 1 row affected (10.01 sec)
mysql> show variables like 'rpl%';
+-------------------------------------------+------------+
| Variable_name | Value |
+-------------------------------------------+------------+
| rpl_read_size | 8192 |
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 10000 | #等待10秒
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_for_slave_count | 1 |
| rpl_semi_sync_master_wait_no_slave | ON |
| rpl_semi_sync_master_wait_point | AFTER_SYNC |
| rpl_stop_slave_timeout | 31536000 |
+-------------------------------------------+------------+
8 rows in set (0.00 sec)
[root@server2 ~]# cd /data/mysql/
[root@server2 mysql]# ls
auto.cnf client-cert.pem mysql server2-relay-bin.000002
binlog.000001 client-key.pem mysql.ibd server2-relay-bin.000003
binlog.000002 #ib_16384_0.dblwr mysql.sock server2-relay-bin.index
binlog.000003 #ib_16384_1.dblwr mysql.sock.lock server-cert.pem
binlog.000004 ib_buffer_pool mysqlx.sock server-key.pem
binlog.000005 ibdata1 mysqlx.sock.lock sys
binlog.000006 ib_logfile0 performance_schema undo_001
binlog.000007 ib_logfile1 private_key.pem undo_002
binlog.index ibtmp1 public_key.pem westos
ca-key.pem #innodb_temp server2.err
ca.pem kaixin server2.pid
[root@server2 mysql]# file server2-relay-bin.000003
server2-relay-bin.000003: MySQL replication log
[root@server2 mysql]# mysqlbinlog -vv server2-relay-bin.000003 #user4的信息没有保存在server2的日志里
server2:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kaixin |
| mysql |
| performance_schema |
| sys |
| westos |
+--------------------+
6 rows in set (0.01 sec)
mysql> use kaixin
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_kaixin |
+------------------+
| user_tb |
+------------------+
1 row in set (0.00 sec)
mysql> select * from user_tb;
+-------+----------+
| name | password |
+-------+----------+
| user1 | 123 |
| user2 | 222 |
| user3 | 333 |
+-------+----------+
3 rows in set (0.00 sec)
server2:
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user_tb;
+-------+----------+
| name | password |
+-------+----------+
| user1 | 123 |
| user2 | 222 |
| user3 | 333 |
| user4 | 444 |
+-------+----------+
4 rows in set (0.00 sec)
server2:
[root@server2 mysql]# vim /etc/my.cnf
relay_log_recovery=ON
[root@server2 mysql]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@server2 mysql]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS!
mysql> show variables like 'relay%';
+---------------------------+-------------------------------------+
| Variable_name | Value |
+---------------------------+-------------------------------------+
| relay_log | server2-relay-bin |
| relay_log_basename | /data/mysql/server2-relay-bin |
| relay_log_index | /data/mysql/server2-relay-bin.index |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | TABLE |
| relay_log_purge | ON |
| relay_log_recovery | ON |
| relay_log_space_limit | 0 |
+---------------------------+-------------------------------------+
8 rows in set (0.01 sec)
组复制
server2:
mysql> show variables like '%repository%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+
2 rows in set (0.00 sec)
mysql> show variables like 'log%';
+----------------------------------------+----------------------------------------+
| Variable_name | Value |
+----------------------------------------+----------------------------------------+
| log_bin | ON |
| log_bin_basename | /data/mysql/binlog |
| log_bin_index | /data/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| log_error | ./server2.err |
| log_error_services | log_filter_internal; log_sink_internal |
| log_error_suppression_list | |
| log_error_verbosity | 2 |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_raw | OFF |
| log_slave_updates | ON |
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_slave_statements | OFF |
| log_statements_unsafe_for_binlog | ON |
| log_throttle_queries_not_using_indexes | 0 |
| log_timestamps | UTC |
+----------------------------------------+----------------------------------------+
19 rows in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'rpl%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'slave_parallel%';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| slave_parallel_type | DATABASE |
| slave_parallel_workers | 0 |
+------------------------+----------+
2 rows in set (0.00 sec)
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.01 sec)
mysql> set global slave_parallel_type=logical_clock;
Query OK, 0 rows affected (0.00 sec)
mysql> set global slave_parallel_workers=16;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 5
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 6772
State: Waiting on empty queue
Info: NULL
*************************** 2. row ***************************
Id: 6
User: system user
Host: connecting host
db: NULL
Command: Connect
Time: 6772
State: Waiting for master to send event
Info: NULL
*************************** 3. row ***************************
Id: 14
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: starting
Info: show processlist
3 rows in set (0.00 sec)
ERROR:
No query specified
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.09 sec)
mysql> show variables like 'slave_parallel%';
+------------------------+---------------+
| Variable_name | Value |
+------------------------+---------------+
| slave_parallel_type | LOGICAL_CLOCK |
| slave_parallel_workers | 16 |
+------------------------+---------------+
2 rows in set (0.00 sec)
mysql> show status like 'rpl%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF |
+----------------------------+-------+
1 row in set (0.00 sec)
========================================
[root@server3 ~]# yum install -y rsync
[root@server3 ~]# cd /usr/local/
[root@server3 local]# mkdir lnmp
[root@server2 ~]# cd /usr/local/lnmp/
[root@server2 lnmp]# ls
mysql
[root@server2 lnmp]# rsync -a mysql server3:/usr/local/lnmp
root@server3's password:
[root@server2 lnmp]# scp /etc/my.cnf server3:/etc/
root@server3's password:
[root@server3 local]# groupadd -g 1001 mysql
[root@server3 local]# useradd -u 1001 -g 1001 -M -d /usr/local/lnmp/mysql/ -s /sbin/nologin mysql
[root@server3 local]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)
[root@server3 local]# vim /etc/my.cnf
server-id=3
[root@server3 local]# cd
[root@server3 ~]# vim .bash_profile
PATH=$PATH:$HOME/bin:/usr/local/lnmp/mysql/bin
[root@server3 ~]# source .bash_profile
[root@server3 ~]# cd /usr/local/lnmp/mysql/
[root@server3 mysql]# ls
bin lib LICENSE-test README run var
docs LICENSE man README.router share
include LICENSE.router mysql-test README-test support-files
[root@server3 mysql]# cd support-files/
[root@server3 support-files]# cp mysql.server /etc/init.d/mysqld
[root@server3 support-files]# mkdir /data/mysql -p
[root@server3 support-files]# chown mysql.mysql /data/mysql/
[root@server3 support-files]# cd /data/mysql/
[root@server3 mysql]# ls
binlog.index server3.err
[root@server3 mysql]# rm -fr *
[root@server3 mysql]# ls
[root@server3 mysql]# mysqld --initialize --user=mysql
2020-08-18T09:00:34.542835Z 0 [System] [MY-013169] [Server] /usr/local/lnmp/mysql/bin/mysqld (mysqld 8.0.21) initializing of server in progress as process 12862
2020-08-18T09:00:34.551733Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-08-18T09:00:36.150733Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-08-18T09:00:38.289287Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: IiKp4syu#3Ag
[root@server3 mysql]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/server3.err'.
. SUCCESS!
[root@server3 mysql]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.21
Copyright (c) 2000, 2020, 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> alter user root@localhost identified by 'Westos+123';
Query OK, 0 rows affected (0.01 sec)
[root@server2 mysql]# mysqldump -u root -pWestos+123 --databases westos --set-gtid-purged=off > dump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
扫一扫
06-16
7426
7426
07-03
7835
7835
05-19
11万+
11万+
10-02
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
