mysql主从服务（主从从结构）_万金油_新浪博客

下午的126分钟开始：

删除所有数据库，只保留系统库

53服务器操作：

[root@host53 mysql]# pwd

/var/lib/mysql

[root@host53 mysql]# systemctl  stop  mysqld.service 

[root@host53 mysql]# rm -rf master.info relay-log.info 

[root@host53 mysql]# rm -rf host53-relay-bin.*

[root@host53 mysql]# systemctl  start   mysqld

52 服务器操作：

[root@host52 mysql]# pwd

/var/lib/mysql

[root@host52 mysql]# systemctl  stop mysqld

[root@host52 mysql]# rm -rf master.info  relay-log.info 

[root@host52 mysql]# rm -rf host52-relay-bin.*

[root@host52 mysql]# systemctl  start  mysqld

[root@host52 mysql]# mysql -uroot -p ——为了保证数据的一致性，把多于的数据库删除，只保留系统库

mysql> drop database gamedb;

Query OK, 1 row affected (0.15 sec)

51服务器操作：

oot@host51 mysql]# systemctl  stop  mysqld.service 

[root@host51 mysql]# rm -rf master51.*

[root@host51 mysql]# ls

auto.cnf  gamedb  ib_buffer_pool  ibdata1  ib_logfile0  ib_logfile1  mysql  performance_schema  xtrabackup_info

[root@host51 mysql]# rm -rf  gamedb/  ——删除库的文件就相当于把库给删了

[root@host51 mysql]# systemctl  restart  mysqld

135分钟哦

52.是从库也是主库

53:

[root@host53 ~]# systemctl  stop  mysqld

[root@host53 ~]# vim /etc/my.cnf

[mysqld]

server_id=53

:wq

[root@host53 ~]# systemctl  start mysqld

[root@host53 ~]# mysql -h192.168.4.52 -uyaya -p123qqq...A

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

4 rows in set (0.00 sec)

mysql> show slave status;

Empty set (0.00 sec)

mysql> change master to

    -> master_host="192.168.4.52",

    -> master_user="yaya",

    -> master_password="123qqq...A",

    -> master_log_file="master52.000001",

    -> master_log_pos=154;

Query OK, 0 rows affected, 2 warnings (0.31 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.4.52

                  Master_User: yaya

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: master52.000001

          Read_Master_Log_Pos: 154

               Relay_Log_File: host53-relay-bin.000002

                Relay_Log_Pos: 319

        Relay_Master_Log_File: master52.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

为共享数据创建数据：

在51数据库操作：

mysql> create database gamedb;

Query OK, 1 row affected (0.04 sec)

mysql> create  table gamedb.a(id int);

Query OK, 0 rows affected (0.27 sec)

mysql> grant select,insert on gamedb.* to testuser@"%" identified by "123qqq...A";

Query OK, 0 rows affected, 1 warning (0.03 sec)

什么原因：

mysql> show master status;

+-----------------+----------+--------------+------------------+-------------------+

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+-----------------+----------+--------------+------------------+-------------------+

| master52.000001 |      154 |              |                  |                   |

+-----------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

[root@host52 mysql]# mysqlbinlog  master52.000001  | grep -insert

5:#180720 16:14:44 server id 52  end_log_pos 123 CRC32 0xbb198393 Start: binlog v 4, server v 5.7.17-log created 180720 16:14:44 at startup

[mysqld]

log_slave_updates

server_id=52

log-bin=master52

binlog_format="mixed

mysql> show slave status\G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.4.52

                  Master_User: yaya

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: master52.000002

          Read_Master_Log_Pos: 720

               Relay_Log_File: host53-relay-bin.000004

                Relay_Log_Pos: 365

        Relay_Master_Log_File: master52.000002

             Slave_IO_Running: Yes

            Slave_SQL_Running: No

    Last_Error: Error 'Table 'gamedb.a' doesn't exist' on query. Default database: 'gamedb'. Query: 'insert into a values(6663)'

报错信息：开了集连，生产环境下应该先开集连

解决方案：

mysql> stop slave;

Query OK, 0 rows affected (0.01 sec)

mysql> create database gamedb;

Query OK, 1 row affected (0.00 sec)

mysql> use gamedb;

Database changed

mysql> create table a(id int);

Query OK, 0 rows affected (0.16 sec)

mysql> start slave;

Query OK, 0 rows affected (0.09 sec)

mysql> select * from a;

+------+

| id   |

+------+

| 6663 |

| 6663 |

+------+

2 rows in set (0.00 sec)

主主结构（互为主从）

不能同时给客户端insert update 的权限，有可能出现冲突

一般跟第三方软件做高可用集群