msmysql 2020-06-06
mysql的主从复制,是用来建立一个和主数据库完全一样的数据库环境,从库会同步主库的所有数据,可轻松实现故障转移。
MySQL主从复制是一个异步的复制过程,主库发送更新事件到从库,从库读取更新记录,并执行更新记录,使得从库的内容与主库保持一致。
binlog:binary log,主库中保存所有更新事件日志的二进制文件。binary log是从数据库服务启动的一刻起,保存数据库所有变更记录(数据库结构和内容)的文件。在主库中,只要有更新事件出现,就会被依次地写入到binary log中,之后会推送到从库中作为从库进行复制的数据源。
binlog输出线程:每当有从库连接到主库的时候,主库都会创建一个线程然后发送binlog内容到从库。 对于每一个即将发送给从库的sql事件,binlog输出线程会将其锁住。一旦该事件被线程读取完之后,该锁会被释放,即使在该事件完全发送到从库的时候,该锁也会被释放。
在从库中,当复制开始时,从库就会创建从库I/O线程和从库的SQL线程进行复制处理。
从库I/O线程:当start slave语句在从库开始执行之后,从库创建一个I/O线程,该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。 从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件,其中包括relay log文件。
从库的SQL线程:从库创建一个SQL线程,这个线程读取从库I/O线程写到relay log的更新事件并执行。
综上所述,可知:
对于每一个主从复制的连接,都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个binlog输出线程,每一个从库都有它自己的I/O线程和SQL线程。
从库通过创建两个独立的线程,使得在进行复制时,从库的读和写进行了分离。因此,即使负责执行的线程运行较慢,负责读取更新语句的线程并不会因此变得缓慢。比如说,如果从库有一段时间没运行了,当它在此启动的时候,尽管它的SQL线程执行比较慢,它的I/O线程可以快速地从主库里读取所有的binlog内容。这样一来,即使从库在SQL线程执行完所有读取到的语句前停止运行了,I/O线程也至少完全读取了所有的内容,并将其安全地备份在从库本地的relay log,随时准备在从库下一次启动的时候执行语句。
node3:master,192.168.48.183 node4:slave, 192.168.48.184
xxxxxxxxxx # 安装好mysql/mariadb数据库: [ ~]# yum install mariadb mariadb-server -y # 修改/etc/my.cnf配置文件,在[mysqld]指令段添加以下行: log-bin=node3-bin server-id=1 # 启动数据库服务: [ ~]# systemctl start mariadb [ ~]# # 查看mysql进程: [ ~]# ps -ef|grep mysqld mysql 6130 1 0 20:37 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr mysql 6316 6130 0 20:37 ? 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock root 6365 5819 0 20:38 pts/0 00:00:00 grep --color=auto mysqld [ ~]# # 查看mysql端口: [ ~]# netstat -ntlp|grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 6316/mysqld [ ~]#
xxxxxxxxxx # 通过mysql直接进入数据库: [ ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.65-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> # 查看log_bin和sql_log_bin是否均为on; MariaDB [(none)]> show variables like "%log_bin"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | | sql_log_bin | ON | +---------------+-------+ 2 rows in set (0.00 sec) MariaDB [(none)]>
xxxxxxxxxx MariaDB [(none)]> grant replication slave on *.* to "superman"@"192.168.48.184" identified by "123456"; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]>
xxxxxxxxxx MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | node3-bin.000004 | 479 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [(none)]>
xxxxxxxxxx # 修改/etc/my.cnf配置文件,在[mysqld]指令块下添加如下行: server-id=2
xxxxxxxxxx [ ~]# systemctl start mariadb
xxxxxxxxxx [ ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 2 Server version: 5.5.65-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. MariaDB [(none)]> MariaDB [(none)]> change master to -> master_host="192.168.48.183", -> master_user="superman", -> master_password="123456", -> master_log_file="node3-bin.000004", -> master_log_pos=479; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]>
xxxxxxxxxx MariaDB [(none)]> slave start; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.48.183 Master_User: superman Master_Port: 3306 Connect_Retry: 60 Master_Log_File: node3-bin.000004 Read_Master_Log_Pos: 479 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: node3-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: 479 Relay_Log_Space: 825 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 1 row in set (0.00 sec) MariaDB [(none)]>
xxxxxxxxxx # 在主库创建一个数据库: MariaDB [(none)]> create database zabbix charset=utf8; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | zabbix | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]> # 在从库查看: MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | zabbix | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]>
xxxxxxxxxx Slave_IO_Running: Connecting # 第一种:主库宕机 # 第二种:从库指定的用户名与密码错误(与主库授权的用户名和密码不一致) # 第三种:关闭防火墙 Slave_IO_Running: No # 从库指定的二进制文件有误 Slave_SQL_Running: No # pos点问题
建议从库数量3-5 为宜,要复制的从节点数量过多,会导致复制延迟。
从库硬件比主库差,导致复制延迟,查看master和slave的系统配置,可能会因为机器配置的问题,包括磁盘IO、CPU、内存等各方面因素造成复制的延迟,一般发生在高并发大数据量写入场景。
主从库之间的网络延迟,主库的网卡、网线、连接的交换机等网络设备都可能成为复制的瓶颈,导致复制延迟。
xxxxxxxxxx # 备份zabbix数据库中的所有表,但是不会自动生成创建zabbix数据库的语句: [ ~]# mysqldump -uroot -p*** zabbix > zabbix_tables.sql [ ~]#
xxxxxxxxxx 备份zabbix数据库中的所有表,并且会生成创建zabbix数据库的SQL语句,也就是导入时不需要先创建数据库: [ ~]# mysqldump -uroot -p*** --databases zabbix > zabbix_database.sql [ ~]#
xxxxxxxxxx [ ~]# mysqldump -uroot -p*** --databases zabbix mysql > zabbix_mysql_database.sql [ ~]#
xxxxxxxxxx [ ~]# mysqldump -uroot -p*** --all-databases > all_databases.sql [ ~]# 或者: [ ~]# mysqldump -uroot -p*** -A > all.sql [ ~]#
xxxxxxxxxx [ ~]# mysqldump -uroot -p*** --master-data zabbix > zabbix_pos.sql [ ~]#
xxxxxxxxxx [ ~]# mysqldump -uroot -p*** --master-data --flush-logs zabbix > zabbix_pos_flush.sql [ ~]#