已删除用户
文章
90
分类
11
评论
0

mysql主从复制

| linux •
字数总计 11021 | 阅读时长 27分钟 | 阅读量 220
master01 192.168.5.140 mysql-5.7 主
slave01 192.168.5.150 mysql-5.7 从

master01 配置：

[root@mysql-master01 ~]# mysql -uroot -pREDhat@123
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 15
Server version: 5.7.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, 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 databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| data1              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> exit
Bye

配置文件

[root@mysql-master01 ~]# cat /etc/my.cnf |grep  -v ^#  |grep -v ^$
[mysqld]
server-id=1   #id 
log-bin=master01-bin  #开启bin-log
binlog-do-db=data1  #需复制的库
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

重启mysqld

[root@mysql-master01 ~]# systemctl restart mysqld

授权slave 用户，主从复制

[root@mysql-master01 ~]# mysql -uroot -pREDhat@123
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 2
Server version: 5.7.41-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, 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> 
mysql> grant replication slave on *.* to slave@192.168.5.150 identified by "REDhat@123";
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql>  flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

从数据库测试是否能用slave用户登录主

[root@mysql-slave01 ~]# mysql -h 192.168.5.140 -uslave -pREDhat@123
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 3
Server version: 5.7.41-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, 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> exit
Bye

复制前保证主从两个数据库数据一致：

把主的原始数据传给从：
例：导出所有数据库：

[root@mysql-master01 ~]# mysqldump -u  root -p -A > all.sql
Enter password: 
[root@mysql-master01 ~]# scp all.sql   192.168.5.150:/root
root@192.168.5.150's password: 
all.sql                                                                                                                 100%  868KB  54.6MB/s   00:00

从数据库导入数据

[root@mysql-slave01 ~]#  mysql -u root -p < all.sql
Enter password: 
[root@mysql-slave01 ~]# mysql -uroot -pREDhat@123
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 7
Server version: 5.7.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, 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 databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| data1              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

主数据库查看master状态

mysql> SHOW MASTER STATUS;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| master01-bin.000001 |      602 | data1        |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

从数据库配置
更改配置文件
添加一个server-id=2

[root@mysql-slave01 ~]# cat /etc/my.cnf |grep  -v ^#  |grep -v ^$
[mysqld]
server-id=2
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

重启服务

[root@mysql-slave01 ~]# mysql -uroot -pREDhat@123
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 2
Server version: 5.7.41 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, 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> change master to master_host='192.168.5.140',master_user='slave',master_password='REDhat@123',master_log_file='master01-bin.000001',master_log_pos=602;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

上面change master 的参数：

MASTER_HOST : 设置要连接的主服务器的ip地址 　　　　

MASTER_USER : 设置要连接的主服务器的用户名 　　　　

MASTER_PASSWORD : 设置要连接的主服务器的密码 　　　

MASTER_LOG_FILE : 设置要连接的主服务器的bin日志的日志名称，即第5步得到的信息 　

MASTER_LOG_POS : 设置要连接的主服务器的bin日志的记录位置

查看主从状态

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.5.140
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master01-bin.000001
          Read_Master_Log_Pos: 602
               Relay_Log_File: mysql-slave01-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: master01-bin.000001
             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: 602
              Relay_Log_Space: 538
              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: d4285b4b-bd55-11ed-bf99-000c29ddc1a3
             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…额 我懒 我就不写了。。。并且配置文件也得开log-bin

my.cnf  参数
log-bin = mysql-bin     #开启mysql的binlog日志功能，binlog日志位置
sync_binlog = 1         #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed   #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7                           #binlog过期清理时间
max_binlog_size = 100m                    #binlog每个日志文件大小
binlog_cache_size = 4m                        #binlog缓存大小

binlog-do-db=test1 #需要同步的数据库
binlog-ignore-db=mysq #不需要同步的库

max_binlog_cache_size= 512m              #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行

auto-increment-offset = 1     # 自增值的偏移量
auto-increment-increment = 1  # 自增值的自增量
slave-skip-errors = all #跳过从库错误

主主参数：

port=3305 将mysql启动端口设置成3305（默认为3306）

log_bin 启动mysql二进制日志，如果没有配置这个将无法远程链接

binlog-ignore 指定不同步的数据库，如果有多个数据库不需要同步可以多个分别声明

character_set_server=utf8 指定utf8为默认字符集

server-id   可以为任意自然数，必须保证两台mysql主机不重复

auto_increment_increment=2   步进值auto_imcrement。一般有n台主MySQL就填n

auto_increment_offset   设定数据库中自动增长的起点，两台mysql的起点必须不同，这样才能避免两台服务器同步时出现主键冲突

replicate-do-db  要同步的数据库，如果需要就填，指定数据库的名称即可，默认为所有库，声明了不同步就默认除了不同步数据库意外的所有库。这里我没写。

log-bin-trust-function-creators=1 在默认情况下mysql会阻止主从同步的数据库function的创建,这会导致我们在导入sql文件时如果有创建function或者使用function的语句将会报错。

本来不打算写这个玩意儿的。。后面我要做statefulset的实验。。还是记录一下。。。mysql-ha明天我争取搞出来。。。还有那个主主。。一般不这样直接用，前面应该放一个负载均衡。。。理论知识网上一堆。专门搞mysql 都迷糊，，何况我不是DBA 哈哈
鸣谢：
主主
主从

文章作者： emporer
本文链接：
版权声明： 本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 Emporer-Linux！
linux linux mysql
喜欢就支持一下吧