mysql的主从的模式:
1.一主多从
主服务器 51操作:
mysql> select user,host from mysql.user;
+-----------+--------------+
| user | host |
+-----------+--------------+
| webadmin | % |
| repluser | 192.168.4.52 |
| king | 192.168.4.53 |
| mysql.sys | localhost |
| root | localhost |
+-----------+--------------+
5 rows in set (0.00 sec)
mysql> use mysql;
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> drop user repluser@192.168.4.52;——删除授权(准备环境)
Query OK, 0 rows affected (0.03 sec)
mysql> drop user king@192.168.4.53;——删除授权(准备环境)
Query OK, 0 rows affected (0.03 sec)
如果有多个从服务器的,可以用这种授权方式
mysql> grant replication slave on *.* to repluser@"%" identified by "123qqq...A";——主库授权
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> show master status; ——查看当前使用的binlong日志信息
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master51.000002 | 4273 | | | |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
确保数据一致性:(把数据进行备份)保证数据与主库的数据是一致
[root@host51 ~]# mysqldump -uroot -p123456 gamedb > /root/gamedb.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host51 ~]# scp /root/gamedb.sql root@192.168.4.53:/root
52/53服务操作:
[root@host53 mysql]# mysql -uroot -p
mysql> create database gamedb;
Query OK, 1 row affected (0.00 sec)
[root@host53 mysql]# mysql -uroot -p123456 gamedb < /root/gamedb.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@host52 mysql]# mysql -uroot -p
mysql> show databases;
mysql> select * from gamedb.t1;
从服务器操作:
52服务器操作:
1.给从服务器指定主服务器:
mysql> change master to
-> master_host="192.168.4.51",
-> master_user="repluser",
-> master_password="123qqq...A",
-> master_log_file="master51.000002",
-> master_log_pos=4273;
Query OK, 0 rows affected, 2 warnings (0.32 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.51
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000002
Read_Master_Log_Pos: 4273
Relay_Log_File: host52-relay-bin.000002
Relay_Log_Pos: 319
Relay_Master_Log_File: master51.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
53:服务器操作:
操作的方法是跟52是一样的
mysql> change master to
-> master_host="192.168.4.51",
-> master_user="relpuser",
-> master_password="123qqq...A",
-> master_log_file="master51.000002",
-> master_log_pos=4273;
Query OK, 0 rows affected, 2 warnings (0.29 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.4.51
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master51.000002
Read_Master_Log_Pos: 5120
Relay_Log_File: host52-relay-bin.000002
Relay_Log_Pos: 1166
Relay_Master_Log_File: master51.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> select * from t1;
注意事项:
保证三个数据库的原始数据是一样的,不管是主服务器还是从服务器,如果有一个数据库的原始数据不一样都回报错。
客户端:检测
检测同步是否一致:三台服务器的数据是一致的(数据同步成功)
50:插入数据
mysql> insert into t1 values(12);
Query OK, 1 row affected (0.09 sec)
51:
mysql> select * from t1 where id=12;
+------+
| id |
+------+
| 12 |
+------+
1 row in set (0.01 sec)
52:
mysql> select * from t1 where id=12;
+------+
| id |
+------+
| 12 |
+------+
1 row in set (0.00 sec)
53:
mysql> select * from t1 where id=12;
+------+
| id |
+------+
| 12 |
+------+
1 row in set (0.00 sec)