视频网站制作教程视频,网站全面详细创建步骤,如何查询域名注册人信息,自助建站设计工作主要包括通常#xff0c;一般业务我们使用云服务器提供的数据库#xff0c;无论是MySQL数据库还是其他数据库#xff0c;云服务厂商都提供了主备功能#xff0c;我们不需要自己配置处理。而如果需要我们自己搭建数据库#xff0c;那么考虑到数据的高可用性、故障恢复和扩展性… 通常一般业务我们使用云服务器提供的数据库无论是MySQL数据库还是其他数据库云服务厂商都提供了主备功能我们不需要自己配置处理。而如果需要我们自己搭建数据库那么考虑到数据的高可用性、故障恢复和扩展性必须做数据备份配置。
一、MySQL 的多种数据备份机制
mysqldump这是 MySQL 自带的备份工具通过导出 SQL 语句来备份数据库。它可以备份整个数据库、特定表或特定数据。使用命令行执行 mysqldump 命令可以生成 SQL 文件然后可以使用该文件还原备份数据。备份命令
# 单个数据库
mysqldump -h hostname -u username -p dbname backup.sql
# 多个数据库
mysqldump -h hostname -u username -p --databases dbname1 dbname2 backup.sqlMySQL Enterprise Backup这是 MySQL 官方提供的高级备份工具【商用收费工具】可用于备份大型数据库。它支持增量备份和并行备份可以在运行时备份数据库减少备份期间的停机时间。它以block级别进行并行备份性能大大优于逻辑备份工具如mysqldump。
mysqlbackup --hosthostname --userusername --passwordpassword --backup-dir/path/to/backupdir backupMySQL WorkbenchMySQL Workbench是一种图形化MySQL管理工具可以进行逻辑备份和还原支持导出SQL脚本、CSV文件和XML文件等。物理备份这种备份方法直接复制数据库文件包括数据文件、日志文件等。可以使用文件系统级别的工具如 rsync 或者文件系统快照功能来备份。复制主从复制 / 多主复制
MySQL主从复制用于将一个MySQL服务器称为主服务器上的数据变更同步到其他MySQL服 务器称为从服务器。主从复制提供了数据备份、读写分离和负载均衡等功能以提高系统的可用性和性能。MySQL多主复制允许在多个数据库实例之间进行双向数据同步。它的工作原理是每个数据库实例都可以充当主服务器和从服务器可以同时接收和发送数据变更。
第三方备份工具还有一些第三方工具可用于备份 MySQL 数据库例如 Percona XtraBackup、MariaDB Backup 等。这些工具提供了更多高级特性如并行备份、压缩备份等。 MySQL 提供的多种数据备份机制各有优缺点和适应场景复制主从复制 / 多主复制适合我们的应用场景多主复制比主从复制会更为复杂一些需要考虑数据冲突等问题。在实际使用过程中主主复制存在很多数据冲突的问题需要解决所以这里我们选择使用主从备份机制。
二、MySQL 主从复制原理 MySQL 主从复制是一种基于日志的复制机制用于将主服务器Master上的数据实时复制到一个或多个从服务器Slave。主从复制的原理如下
主服务器将所有修改操作记录在二进制日志Binary Log中。这些修改可以是插入、更新或删除数据的操作。从服务器连接到主服务器并发送一个请求请求成为主服务器的从服务器。主服务器收到请求后将记录从服务器的信息并开始与从服务器建立复制连接。主服务器将二进制日志中的内容发送给从服务器。从服务器接收并执行这些日志中的操作将数据修改操作反映到自己的数据库上。从服务器还会定期向主服务器发送心跳信息以维持连接。如果主服务器长时间没有收到从服务器的心跳信息就认为从服务器宕机不再向其发送日志。如果主服务器发生故障导致无法提供服务可以将一个从服务器提升为新的主服务器以继续提供服务。此时其他从服务器将切换到新的主服务器上进行复制。 通过主从复制可以实现数据的实时复制和分布式读取提高数据库的可用性和读取性能。此外主从复制还可以用于备份数据当主服务器发生故障时可以快速切换到从服务器减少服务停机时间。 需要注意的是主从复制是异步的从服务器的数据可能稍有延迟。而且主从复制只复制数据修改操作不复制表结构的变更。如果需要同步表结构的变更可以使用主从复制搭配其他工具如 GTIDGlobal Transaction Identifier或者基于触发器的解决方案。
三、MySQL 主主复制原理 MySQL主主复制是一种数据同步和高可用性解决方案它能够保持多个MySQL服务器之间的数据一致性。主主复制的原理如下
配置双向复制在两台MySQL服务器上分别配置主从复制使每台服务器都可以同时充当主服务器和从服务器。生成二进制日志当有数据更新操作如插入、更新、删除时MySQL服务器会将这些操作记录在二进制日志中。传递二进制日志每台服务器将自己的二进制日志传递给另一台服务器。这可以通过网络连接实现通常使用基于TCP/IP的复制协议。应用二进制日志每台服务器接收到对方的二进制日志后会将这些日志应用到自己的数据库中从而使两台服务器的数据保持一致。处理冲突在主主复制中由于两台服务器都可以接收写操作可能会出现冲突。为了处理冲突MySQL提供了自动事务回滚和主键冲突检测等机制。
三、使用Docker Compose安装MySQL 主从服务器
1. 环境准备 首先准备至少2台Linux服务器一台作为MySQL主服务器一台或者多台作为MySQL从服务器。我们这里准备两台服务器分别为
192.168.0.210 (MySQL主服务器)192.168.0.195 (MySQL从服务器)
2. 准备MySQL文件存放目录
准备数据库存储目录在两台主从服务器上分别执行一下命令
mkdir -p /opt/container/mysql/data /opt/container/mysql/config /opt/container/mysql/slave/mysql-files chmod -R 777 /opt/container/mysql/data /opt/container/mysql/config /opt/container/mysql/slave/mysql-files /opt/container/mysql/data 用于存放MySQL数据文件 /opt/container/mysql/config 用于存放MySQL配置文件 /opt/container/mysql/slave/mysql-files 用于存放MySQL数据导入/导出的数据文件存放目录
3. MySQL主、从服务器docker-compose-mysql.yml文件
version: 3
services:mysql:environment:## root账号的密码MYSQL_ROOT_PASSWORD: root密码TZ: Asia/Shanghai## 新建mysql账号MYSQL_USER: mysql_userMYSQL_PASSWORD: mysql_user密码MYSQL_DATABASE: mysql_dbimage: docker.io/mysql:latest container_name: mysqlrestart: always## 映射挂载volumes:## 数据目录要确保先创建好- /opt/container/mysql/data:/var/lib/mysql- /opt/container/mysql/config/my.cnf:/etc/mysql/my.cnf- /opt/container/mysql/slave/mysql-files:/var/lib/mysql-files- /etc/localtime:/etc/localtime- /usr/share/zoneinfo/Asia/Shanghai:/etc/timezone## 初始化的脚本初始化我们存放的init.sql文件- ./mysql:/docker-entrypoint-initdb.d/ports:- 3306:3306command:--max_connections1000--character-set-serverutf8mb4--collation-serverutf8mb4_general_ci--default-authentication-pluginmysql_native_password4. 在MySQL主服务器上新增配置文件
在/opt/container/mysql/config目录下新增my.cnf文件 配置文件内容
注意主从服务器 server-id 一定要配置为不一样在这里主服务器的server-id设置为150
[mysqld]
pid-file /var/run/mysqld/mysqld.pid
socket /var/run/mysqld/mysqld.sock
datadir /var/lib/mysql
secure-file-priv NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links0# Custom config should go here
!includedir /etc/mysql/conf.d/sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
server-id150
log-bin/var/lib/mysql/mysql-bin
expire_logs_days60
binlog-formatmixed
max_allowed_packet256M
relay-logmysql-relay
log-slave-updates
auto_increment_increment2 #表示自增长字段每次递增的量
auto_increment_offset1 #表示自增长字段从那个数开始5. 在MySQL从服务器上新增配置文件
在/opt/container/mysql/config目录下新增my.cnf文件 配置文件内容
注意主从服务器 server-id 一定要配置为不一样在这里从服务器的server-id设置为200从服务器需要设置为read_only 1只读模式这里为了测试后面的主主复制先不设置实际应用中一定要设置。
[mysqld]
pid-file /var/run/mysqld/mysqld.pid
socket /var/run/mysqld/mysqld.sock
datadir /var/lib/mysql
secure-file-priv NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links0# Custom config should go here
!includedir /etc/mysql/conf.d/sql_modeSTRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
server-id200
# 设置只读模式
# read_only 1
log-bin/var/lib/mysql/mysql-bin
expire_logs_days60
binlog-formatmixed
max_allowed_packet256M
relay-logmysql-relay
log-slave-updates
auto_increment_increment2 #表示自增长字段每次递增的量
auto_increment_offset1 #表示自增长字段从那个数开始6. 在两台MySQL主备服务器上分别执行docker-compose安装启动命令
将docker-compose-mysql.yml上传至/opt/software目录这个目录可以自己选择然后到目录下执行安装启动命令
docker-compose -f docker-compose-mysql.yml up -d[rootlocalhost software]# docker-compose -f docker-compose-mysql.yml up -d
[] Running 13/13⠿ mysql Pulled 40.4s⠿ 72a69066d2fe Pull complete 14.2s⠿ 93619dbc5b36 Pull complete 14.2s⠿ 99da31dd6142 Pull complete 14.6s⠿ 626033c43d70 Pull complete 14.7s⠿ 37d5d7efb64e Pull complete 14.7s⠿ ac563158d721 Pull complete 16.2s⠿ d2ba16033dad Pull complete 16.2s⠿ 688ba7d5c01a Pull complete 16.2s⠿ 00e060b6d11d Pull complete 24.5s⠿ 1c04857f594f Pull complete 24.5s⠿ 4d7cfa90e6ea Pull complete 24.6s⠿ e0431212d27d Pull complete 24.6s
WARN[0040] Found orphan containers ([nginx]) for this project. If you removed or renamed this service in your compose file, you can run this command with the --remove-orphans flag to clean it up.
[] Running 1/1⠿ Container mysql Started 0.3s通过docker ps命令可以看到mysql已经安装并启动成功
[rootlocalhost software]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bf4e482dbc71 mysql:latest docker-entrypoint.s… 21 minutes ago Up 21 minutes 0.0.0.0:3306-3306/tcp, :::3306-3306/tcp, 33060/tcp mysql四、MySQL 主从复制配置及测试
1. 登录主MySQL服务器查看配置数据
进入docker容器
[rootlocalhost software]# docker exec -it bf4e482dbc71 bash通过用户名密码登录mysql账户
rootbf4e482dbc71:/# mysql -uroot -p密码查看需同步的主服务器数据 show master status在MySQL从服务器上配置时需要用到File和Position的值
mysql show master status;
-------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-------------------------------------------------------------------------------
| mysql-bin.000003 | 156 | | | |
-------------------------------------------------------------------------------
1 row in set (0.00 sec)2. 登录从MySQL服务器并配置数据同步
进入docker容器
[rootlocalhost software]# docker exec -it b168db7981c0 bash通过用户名密码登录mysql账户
rootbf4e482dbc71:/# mysql -uroot -p密码登录成功后执行从主数据库同步的配置命令
CHANGE MASTER TO master_host 192.168.0.210,master_port 3306,master_user root,master_password 密码,master_log_file mysql-bin.000003,master_log_pos 156;启动从服务器
mysql start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)使用命令 show slave status\G 查看从服务器状态, 以下两项显示Yes表示配置成功 Slave_IO_Running: Yes Slave_SQL_Running: Yes
mysql show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.0.210Master_User: rootMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 156Relay_Log_File: mysql-relay.000002Relay_Log_Pos: 324Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 156Relay_Log_Space: 529Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 150Master_UUID: ce0ecbd8-667b-11ee-98e5-0242ac120003Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace:
1 row in set, 1 warning (0.00 sec)3. 主从数据库复制测试
在MySQL主服务器新建一个数据库my_test
mysql create database my_test;
Query OK, 1 row affected (0.01 sec)在MySQL从服务器执行查看数据库命令可以看到my_test数据库已经同步到MySQL从服务器
mysql show databases;
--------------------
| Database |
--------------------
| dbwl |
| information_schema |
| my_test |
| mysql |
| performance_schema |
| sys |
--------------------
6 rows in set (0.00 sec)
在MySQL主服务器新建一个表t_test并新增一条数据
mysql use my_test;
Database changedmysql create table t_test(id int UNSIGNED NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, PRIMARY KEY (id));
Query OK, 0 rows affected (0.04 sec)mysql insert into t_test(name) values(Test);
Query OK, 1 row affected (0.02 sec)在MySQL从服务器查询数据库和表数据是否同步如果配置正常我们可以看到在MySQL主服务器新增的数据库和表数据在从服务器也存在。
mysql show databases;
--------------------
| Database |
--------------------
| dbwl |
| information_schema |
| my_test |
| mysql |
| performance_schema |
| sys |
--------------------
6 rows in set (0.00 sec)mysql use my_test;
Database changed
mysql show tables;
-------------------
| Tables_in_my_test |
-------------------
| t_test |
-------------------
1 row in set (0.00 sec)mysql select * from t_test;
----------
| id | name |
----------
| 1 | Test |
----------
1 row in set (0.00 sec)
请注意如果是主从复制那么一定不要在从服务器进行写或删除操作将从服务器配置为只读否则数据将不再进行同步。
五、MySQL 主主复制配置及测试 MySQL 主主复制的配置和主从复制基本一样只是需要将原本在从服务器执行的配置命令在主服务器上再执行一遍。
1. 配置主主同步
在从服务器上查看状态 show master status在MySQL从服务器上配置时需要用到File和Position的值
mysql show master status;
-------------------------------------------------------------------------------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
-------------------------------------------------------------------------------
| mysql-bin.000003 | 2581 | | | |
-------------------------------------------------------------------------------
1 row in set (0.00 sec)
在主服务器上执行同步配置命令
CHANGE MASTER TO master_host 192.168.0.195,master_port 3306,master_user root,master_password 密码,master_log_file mysql-bin.000003,master_log_pos 2581;在主服务器上执行start slave;
mysql start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)使用命令 show slave status\G 查看从服务器状态, 以下两项显示Yes表示配置成功 Slave_IO_Running: Yes Slave_SQL_Running: Yes
mysql show slave status\G
*************************** 1. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 192.168.0.195Master_User: rootMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 2581Relay_Log_File: mysql-relay.000002Relay_Log_Pos: 324Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 2581Relay_Log_Space: 529Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 200Master_UUID: c1d65f95-667e-11ee-bc7f-0242ac120003Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_Namespace:
1 row in set, 1 warning (0.00 sec)2. 主主数据库复制测试为了方便区分下面仍以前面的主/从服务器命名进行区分
在MySQL从服务器新建一个数据库my_test_slave
mysql create database my_test_slave;
Query OK, 1 row affected (0.01 sec)在MySQL主服务器执行查看数据库命令可以看到my_test_slave数据库已经同步到MySQL主服务器
mysql show databases;
--------------------
| Database |
--------------------
| dbwl |
| information_schema |
| my_test |
| my_test_slave |
| mysql |
| performance_schema |
| sys |
--------------------
7 rows in set (0.00 sec)
在MySQL从服务器的my_test_slave数据库新建一个表t_test并新增一条数据
mysql use my_test_slave;
Database changedmysql create table t_test(id int UNSIGNED NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, PRIMARY KEY (id));
Query OK, 0 rows affected (0.04 sec)mysql insert into t_test(name) values(Test);
Query OK, 1 row affected (0.02 sec)在MySQL主服务器查询数据库和表数据是否同步如果配置正常我们可以看到在MySQL从服务器新增的数据库和表数据在主服务器也存在。
mysql show databases;
--------------------
| Database |
--------------------
| dbwl |
| information_schema |
| my_test |
| my_test_slave |
| mysql |
| performance_schema |
| sys |
--------------------
7 rows in set (0.00 sec)mysql use my_test_slave;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql show tables;
-------------------------
| Tables_in_my_test_slave |
-------------------------
| t_test |
-------------------------
1 row in set (0.00 sec)mysql select * from t_test;
----------
| id | name |
----------
| 1 | Test |
----------
1 row in set (0.00 sec)
测试在主数据库插入一条数据然后在从数据库查询查看是否能够同步
mysql insert into t_test(name) values(TestMaster);
Query OK, 1 row affected (0.02 sec)在从数据库执行查询命令可以看到数据也同步过来了说明主主复制生效。
mysql select * from t_test;
----------------
| id | name |
----------------
| 1 | Test |
| 3 | TestMaster |
----------------
2 rows in set (0.00 sec)很多业务场景中大多数人使用主主复制keepalived来实现MySQL服务器的高可用但是存在很大的问题是处理数据冲突问题可以通过my.cnf中配置id自增来解决
auto_increment_increment2 #表示自增长字段每次递增的量
auto_increment_offset1 #表示自增长字段从那个数开始在实际业务处理中会更加复杂所以在数据库到底是使用主从复制还是主主复制需要根据自己的业务场景选择。
