原理图：
主从复制的原理总结一下：主服务器有更新的数据写入 binary log中，从服务器开启 io线程 不断的探视这个binary log文件，有新内容更新了就同步复制到从服务器的relay log中，使用推模式，并非拉模式，从服务器使用开启的sql 线程把relay log中的更新内容回放到本地数据库，这样就走完了主从复制的完整流程。 注意：主服务器负责更新，从服务器负责读（最好不要有更新操作）。
1.两台mysql服务器安装同一版本mysql数据库:
sudo apt install mysql mysql-server
2.启动mysql服务:
sudo service mysql start
3.分别在两个数据库中创建同样的库和表(创建库和表在此略过)
主服务器( localhost1这台服务器):
4.修改主mysql配置文件, 支持主从模式, 主要配置如下几个参数(binlog_do_db记录了需要做主从同步的数据库名称):
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
添加如下内容:
server-id = 118
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = flarum （这是指定数据库，如果不指定就是 all数据库）
5.重启主数据库
sudo service mysql restart
6.登录主mysql服务器, 并在mysql控制台下输入授权主从模式的命令(此处使用的是root用户, 授权192.168.20.40使用flarum库, 如果想凭用户密码登录, 可以授权’%’)
mysql -u root -p
6-1：
grant replication slave, replication client on *.* to 'copy'@'%' identified by '123456';
6-2：
grant all on *.* to copy@'%' identified by '123456';
6-3:
show master status;
建议重置一下主服务状态，执行命令：
reset master;
从服务器(localhsot2这台服务器):
7.修改从mysql配置文件, 支持主从模式, 主要配置如下几个参数(binlog_do_db记录了需要做主从同步的数据库名称):
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
添加如下内容:
server-id = 40
log_bin = /var/log/mysql/mysql-bin.log
binlog_do_db = flarum (可以不加)
8.登录从mysql服务器, 并在mysql控制台下输入匹配主从模式的命令(此处使用的是root用户, 将当前数据库作为从库匹配到主数据库192.168.20.190的mysql服务上)
$ mysql -u root -p
8-1:
// master_host:主服务器ip； master_user: 主服务器用户，这个用户专门来拷贝数据到从库使用的； master_log_file：主库的二进制文件；
// master_log_pos :数据库偏移量，也就是从主库什么存储位置开始拷贝数据到从库。 154是指从主库的最初数据，空库的时候之后，有第一条数据开始同步数据的。
change master to master_host='localhost1', master_user='copy', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=154;
8-2：
// 重启mysql
service mysql restart
8-3:
// 查询从机器状态
mysql -uroot -proot
show slave status\G;
8-4:
查询结果显示如下：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.2.34
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: gtgpu-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: 154
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: /var/lib/mysql/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)
ERROR:
No query specified
mysql> exit;
Bye
root@gtgpu:/var/log/cups# sudo service mysql restart
sudo: error in /etc/sudo.conf, line 0 while loading plugin `sudoers_policy'
sudo: /usr/lib/sudo/sudoers.so must be only be writable by owner
sudo: fatal error, unable to load plugins
root@gtgpu:/var/log/cups# service mysql restart
root@gtgpu:/var/log/cups#
root@gtgpu:/var/log/cups# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.33-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.34
Master_User: copy
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 446
Relay_Log_File: gtgpu-relay-bin.000005
Relay_Log_Pos: 659
Relay_Master_Log_File: mysql-bin.000003
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: 446
Relay_Log_Space: 21139
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: 34
Master_UUID: 3ee2cf7e-8232-11eb-a6ec-a4bf01709a31
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)
注：Slave_IO及 Slave_SQL进程必须正常运行，即 YES状态，否则都是错误的状态。
// 在配置文件，设置库编码支持中文
character-set-server=utf8
// 表忽略大小写
lower_case_table_names=1
auto_increment_increment=2 步长，两个主库步长就为2
auto_increment_offset=2 组件开始 防止主键冲突，两个主库步长就为2
总结： 改完上面要重启mysql服务器
service mysql restart
mysql -uroot -proot 进入 shut down;(关闭服务) ;登录服务 : mysql -uroot -proot -P3306 -h127.0.0.1
809
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
