centos mysql的主从架构（部分命令粘贴可能会出错，建议手敲）

最新推荐文章于 2024-08-02 17:35:27 发布

liu1251303815

最新推荐文章于 2024-08-02 17:35:27 发布

阅读量108

点赞数

分类专栏： java编程 mysql 文章标签： ms mysql linux

本文链接：https://blog.csdn.net/liu1251303815/article/details/83010123

版权

java编程同时被 2 个专栏收录

21 篇文章 0 订阅

订阅专栏

mysql

8 篇文章 0 订阅

订阅专栏

1、安装MySQL（已写过）

2、启动服务

service mysqld start;(centos7 : systemctl start mysqld.service)

3、授权远程登陆（允许所有用户以root登陆）

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

4、刷新权限列表

FLUSH PRIVILEGES;

5、修改主配置文件(/etc/my.cnf【注意备份】)

[mysqld]
#开启日志
log-bin=mysql-bin
#唯一标识
server-id=129
#数据路径
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#要同步的数据库 可以写多个
binlog-do-db=lch

6、重启服务

service mysql restart (systemctl restart mysqld.service <centos7>)

7、进入数据库查看状态(file position的值有用需要记录) 主机配置完毕

mysql> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000004
        Position: 208
    Binlog_Do_DB: lch
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

8、配置从机（/etc/my.cnf 【提前备份】）

[mysqld]
#唯一标识 建议为ip
server-id=130

9、重启服务

service mysql restart (systemctl restart mysqld.service <centos7>)

10、重新配置slave(粘贴容易出错最好手动敲)master_log_file就是之前【show master status\G】命令查看的File master_log_pos 就是 Position

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.227.129',master_user='root',master_password='root',master_log_file='mysql-bin.000003',master_log_pos=106;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

11、查看状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.227.129
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 269
        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: 106
              Relay_Log_Space: 443
              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: 129
                  Master_UUID: 
             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 the slave I/O thread to update it
           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
1 row in set (0.01 sec)

ERROR: 
No query specified

12、踩坑主从数据必须完全相同或者主库中有的从库中必须有，否则会出错（主库进行关于某个从库中没有的表进行操作的时候，从库没有这个表，会包sql异常。导致从库宕机）

解决方案：若已经宕机，删除主库日志，重新更改从库读取的日志，从库备份主库目录

12.1删除日志

[root@localhost mysql]# cd /var/lib/mysql/
[root@localhost mysql]# ls
ibdata1      ib_logfile1  mysql             mysql-bin.index  test
ib_logfile0  lch          mysql-bin.000001  mysql.sock
[root@localhost mysql]# rm -rf mysql-bin.*
[root@localhost mysql]# ls
ibdata1  ib_logfile0  ib_logfile1  lch  mysql  mysql.sock  test
[root@localhost mysql]#

12.2重启服务

[root@localhost mysql]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@localhost mysql]#

12.3 查看file 和 position

[root@localhost mysql]# mysql -u root -proot
mysql> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 106
    Binlog_Do_DB: lch
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

12.4备份数据

[root@localhost mysql]# mysqldump -u root -p lch > ~/mydb.sql
Enter password: 
[root@localhost mysql]#

12.5从机同步主机数据（没有权限 ssh不允许root远程登陆）

[root@localhost ~]# scp  root@192.168.227.129:/root/mydb.sql  /root/mydb.sql
root@192.168.227.129's password: 
Permission denied, please try again.
root@192.168.227.129's password:

12.5.1在主库机器新添用户test 将mydb.sql移动到test家下并授权给test

[root@localhost ~]# mv ~/mydb.sql /home/test/
[root@localhost ~]# ll /home/test/mydb.sql 
-rw-r--r--. 1 root root 1843 Oct 11 23:40 /home/test/mydb.sql
[root@localhost ~]# chown  test:test /home/test/mydb.sql 
[root@localhost ~]# ll /home/test/mydb.sql 
-rw-r--r--. 1 test test 1843 Oct 11 23:40 /home/test/mydb.sql
[root@localhost ~]#

12.5.2 远程拷贝sql文件

[root@localhost ~]# scp  test@192.168.227.129:/home/test/mydb.sql  /root/mydb.sql
test@192.168.227.129's password: 
mydb.sql                                                                                                                                                 100% 1843     1.5MB/s   00:00    
[root@localhost ~]#

12.5.3同步数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lch                |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> use lch
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> source /root/mydb.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

12.6重新绑定日志文件

mysql> 
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to master_host='192.168.227.129',master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=342;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

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

over