下面为大家演示mysql主从搭建的基本步骤:
主库ip:10.10.10.168 备库ip:10.10.10.151 mysql版本:5.6.38
一、主库参数设置,修改主库my.cnf,在【mysqld】下设置如下两个参数:(修改完重启使参数生效)
[mysqld]
server_id=1 #设置主库的server id,需要和备库不相同
log_bin=mysql-bin #开启主库binlog日志功能,用于同步数据
二、备库参数设置,修改备库my.cnf , 在【mysqld 】添加server_id 参数 即可 :(修改完重启使参数生效)
[mysqld]
server_id=2 #设置备库的server id,需要和主库不相同
三、在主库上锁表并进行备份:
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
[root@test-168 server]#/home/server/mysql5.6/bin/mysqldump -uroot -p --all-database >all.sql
四、查看主库binlog点,恢复主库写操作,并在主库授权备库复制账号密码和权限:
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 120 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to rep_user@10.10.10.151 identified by 'password';
Query OK, 0 rows affected (0.00 sec)
五、在备库上设置主库ip、复制所需用户、密码、执行复制的binlog文件和位置等:
mysql>change master to master_host='10.10.10.168',\
master_port=63306,\
master_user='rep_user',\
master_password='password',\
master_log_file='mysql-bin.000001',\
master_log_pos=120;
六、在从库导入主库之前的备份:
[root@test-151 server]# /home/server/mysql5.6/bin/mysql <all.sql
七、备库开启slave功能,并查看slave状态:
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: 10.10.10.168
Master_User: rep_user
Master_Port: 63306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 333
Relay_Log_File: test-151-relay-bin.000002
Relay_Log_Pos: 496
Relay_Master_Log_File: mysql-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: 333
Relay_Log_Space: 672
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: 8c8e1124-0d61-11e8-b4c7-005056b011e9
Master_Info_File: /home/server/mysql5.6/data/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.00 sec)
重点是查看 Slave_IO_Running: Yes Slave_SQL_Running: Yes 说明复制进程和应用进程正常
七、主库建表插入数据,备库检测是否同步:
(一)、主库写入数据:
mysql> use test;
Database changed
mysql> create table t1 ( x int,y varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values( 1,'A');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| x | y |
+------+------+
| 1 | A |
+------+------+
1 row in set (0.00 sec)
(二)、备库查看同步结果:
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+------+
| x | y |
+------+------+
| 1 | A |
+------+------+
1 row in set (0.00 sec)
测试正常,数据正常同步,搭建基本完成。
主库ip:10.10.10.168 备库ip:10.10.10.151 mysql版本:5.6.38
一、主库参数设置,修改主库my.cnf,在【mysqld】下设置如下两个参数:(修改完重启使参数生效)
[mysqld]
server_id=1 #设置主库的server id,需要和备库不相同
log_bin=mysql-bin #开启主库binlog日志功能,用于同步数据
二、备库参数设置,修改备库my.cnf , 在【mysqld 】添加server_id 参数 即可 :(修改完重启使参数生效)
[mysqld]
server_id=2 #设置备库的server id,需要和主库不相同
三、在主库上锁表并进行备份:
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
[root@test-168 server]#/home/server/mysql5.6/bin/mysqldump -uroot -p --all-database >all.sql
四、查看主库binlog点,恢复主库写操作,并在主库授权备库复制账号密码和权限:
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 120 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to rep_user@10.10.10.151 identified by 'password';
Query OK, 0 rows affected (0.00 sec)
五、在备库上设置主库ip、复制所需用户、密码、执行复制的binlog文件和位置等:
mysql>change master to master_host='10.10.10.168',\
master_port=63306,\
master_user='rep_user',\
master_password='password',\
master_log_file='mysql-bin.000001',\
master_log_pos=120;
六、在从库导入主库之前的备份:
[root@test-151 server]# /home/server/mysql5.6/bin/mysql <all.sql
七、备库开启slave功能,并查看slave状态:
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: 10.10.10.168
Master_User: rep_user
Master_Port: 63306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 333
Relay_Log_File: test-151-relay-bin.000002
Relay_Log_Pos: 496
Relay_Master_Log_File: mysql-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: 333
Relay_Log_Space: 672
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: 8c8e1124-0d61-11e8-b4c7-005056b011e9
Master_Info_File: /home/server/mysql5.6/data/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.00 sec)
重点是查看 Slave_IO_Running: Yes Slave_SQL_Running: Yes 说明复制进程和应用进程正常
七、主库建表插入数据,备库检测是否同步:
(一)、主库写入数据:
mysql> use test;
Database changed
mysql> create table t1 ( x int,y varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values( 1,'A');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| x | y |
+------+------+
| 1 | A |
+------+------+
1 row in set (0.00 sec)
(二)、备库查看同步结果:
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+------+
| x | y |
+------+------+
| 1 | A |
+------+------+
1 row in set (0.00 sec)
测试正常,数据正常同步,搭建基本完成。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31135825/viewspace-2151032/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31135825/viewspace-2151032/