-
Notifications
You must be signed in to change notification settings - Fork 0
MySQL 主从
yangyp8110 edited this page Jan 17, 2018
·
1 revision
准备两台服务器：
- 192.168.74.129 master
- 192.168.74.131 slave
如果还未安装，请参考： Mysql安装
登陆192.168.74.129，配置mysql,编辑my.cnf(
vi /etc/my.cnf)，在[mysqld]下面加入如下配置
log-bin=mysql-binlog
server-id=1
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-do-db=test_db
说明
- log-bin指定了log文件，文件位于位置
/var/lib/mysql,以mysql-binlog开头的文件 - 这里的server-id用于标识唯一的数据库，这里设置为1，在设置从库的时候就需要设置为其他值。
- binlog-ignore-db：表示同步的时候ignore的数据库
- binlog-do-db：指定需要同步的数据库
配置salve读取master的log文件权限
- 然后重启mysql：systemctl restart mysqld
- 登陆mysql：[root@yyp local]# mysql -uroot -proot 回车。
- 赋予从库权限帐号，允许用户在主库上读取日志，赋予192.168.74.131也就是Slave机器有File权限，只赋予Slave机器有File权限还不行，还要给它REPLICATION SLAVE的权限才可以。 在Master数据库命令行中输入：（下例子使用的是root用户作为同步用户，真实使用时可以自己设置）
mysql> GRANT FILE ON *.* TO 'root'@'192.168.74.131' IDENTIFIED BY 'root';
Query OK, 1 row affected (0.05 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.74.131' IDENTIFIED BY 'root';
Query OK, 1 row affected (0.05 sec)
mysql> FLUSH PRIVILEGES
Query OK, 1 row affected (0.05 sec)
//新增了用户：
mysql> select user,host from user;
+-----------+----------------+
| user | host |
+-----------+----------------+
| root | 192.168.74.131 |
| mysql.sys | localhost |
| root | localhost |
+-----------+----------------+
3 rows in set (0.00 sec)
mysql> 重启mysql，登陆mysql查看master状态：
mysql> show master status;
+---------------------+----------+--------------+---------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-binlog.000002 | 154 | test_db | information_schema,performance_schema,mysql | |
+---------------------+----------+--------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)
mysql> 这里的 File 、Position 是在配置Salve的时候要使用到的，Binlog_Do_DB表示要同步的数据库，Binlog_Ignore_DB 表示Ignore的数据库，这些都是在配置的时候进行指定的。(如果未显示信息或未显示test_db库信息，表示my.cnf配置未生效)
配置my.cnf(
vi /etc/my.cnf)在[mysqld]下面加入以下配置：
log-bin=mysql-binlog
server-id=2
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
replicate-do-db=test_db
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60replicate-do-db ： 需要同步的库
- 注意点：在MySQL5.6之后的版本中没有指定以下内容,不需要指定
master-host=192.168.74.129 #Master的主机IP
master-user=root
master-password=mysql password #Master的MySQL密码
修改完my.cnf，重启mysqld，登陆salve mysql，执行以下配置：
mysql> stop slave; #关闭Slave
mysql> change master to master_host='192.168.74.129',master_user='root',master_password='root',master_log_file='mysql-binlog.000002', master_log_pos=154;
mysql> start slave; #开启Slave
Query OK, 0 rows affected, 1 warning (0.00 sec)- 说明：
- master_host ： master的ip
- master_user ： master用户名
- master_password ： master密码
- master_log_file ： 从master登陆mysql，
show master status展示的File字段 - master_log_pos ： 从master登陆mysql，
show master status展示的Position字段
可以查看slave的配置信息：
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.74.129
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-binlog.000002
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 323
Relay_Master_Log_File: mysql-binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test_db
Replicate_Ignore_DB: mysql
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: 534
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: 1dcdc2d2-6651-11e7-a8c0-000c29ef6d9e
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)
ERROR:
No query specified
mysql> 从库建好 test_db ,然后master做任何操作，都会同步到slave
- master建person表
mysql> show tables;
Empty set (0.00 sec)
mysql> create table person(pId INT NOT NULL AUTO_INCREMENT,pName VARCHAR(100) NOT NULL, PRIMARY KEY(pId))ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| person |
+-------------------+
1 row in set (0.00 sec)
mysql>
查看slave：
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| person |
+-------------------+
1 row in set (0.00 sec)
mysql> - master插入数据：
mysql> insert into person values(1,'123');
Query OK, 1 row affected (0.03 sec)
mysql> insert into person values(2,'333');
Query OK, 1 row affected (0.00 sec)
mysql> select * from person;
+-----+-------+
| pId | pName |
+-----+-------+
| 1 | 123 |
| 2 | 333 |
+-----+-------+
2 rows in set (0.00 sec)
mysql>
查看slave：
mysql> select * from person;
+-----+-------+
| pId | pName |
+-----+-------+
| 1 | 123 |
| 2 | 333 |
+-----+-------+
2 rows in set (0.00 sec)
mysql>