一、mysql的安裝配置

mysql安裝和配置可參考博主的部落格：

http://blog.itpub.net/31015730/viewspace-2152429/
http://blog.itpub.net/31015730/viewspace-2152272/

二、mysql主從複製

場景描述：
主資料庫伺服器：10.10.1.129，MySQL已經安裝，並且無應用資料。
從資料庫伺服器：10.10.1.130，MySQL已經安裝，並且無應用資料。

1、主伺服器上配置

[root@wjq1 yum.repos.d]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.36-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

mysql> GRANT REPLICATION SLAVE ON *.* to 'rep-user'@'10.10.1.130' identified by 'beijing';

Query OK, 0 rows affected (0.03 sec)

mysql> select host,user,password from mysql.user;

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

| host | user | password |

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

| localhost | root | *63D90C8BB77C99F7F5D836C5AE2D1E9BE5CE43C2 |

| wjq1 | root | |

| 127.0.0.1 | root | |

| ::1 | root | |

| localhost | | |

| wjq1 | | |

| % | root | *E94A9AEB5F3D9594EE8BFEBAE2E75B1E0694484A |

| 10.10.1.130 | rep-user | *63D90C8BB77C99F7F5D836C5AE2D1E9BE5CE43C2 |

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

8 rows in set (0.00 sec)

mysql> show master status;

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

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

| mysql3306.000003 | 766 | | | |

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

1 row in set (0.00 sec)

2、從伺服器上配置

[root@wjq2 yum.repos.d]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.36-log MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> change master to

-> master_host=10.10.1.129,

-> master_user=rep-user,

-> master_password=beijing,

-> master_log_file=mysql3306.000003,

-> master_log_pos=766;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10.10.1.129,

master_user=rep-user,

master_password=beijing,

master_log_file=mysq' at line 2

mysql>

mysql> change master to

-> master_host='10.10.1.129',

-> master_user='rep-user',

-> master_password='beijing',

-> master_log_file='mysql3306.000003',

-> master_log_pos='766';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''766'' at line 6

【注意：出現上面兩個報錯的原因：master_host和master_log_file的地址必須使用單引號引起來；而master_log_pos不需要使用引號引起來】

mysql> change master to

-> master_host='10.10.1.129',

-> master_user='rep-user',

-> master_password='beijing',

-> master_log_file='mysql3306.000003',

-> master_log_pos=766;

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

mysql>

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: 10.10.1.129

Master_User: rep-user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql3306.000003

Read_Master_Log_Pos: 766

Relay_Log_File: wjq2-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql3306.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: 766

Relay_Log_Space: 455

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: 3306

Master_UUID: 250ef797-36b3-11e8-8236-000c29f47fc4

Master_Info_File: /data/mysql/mysql_3306/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)

ERROR:

No query specified

【注意：Slave_IO_Running和Slave_SQL_Running這兩處都為Yes表示同步成功】

3、驗證主從複製的效果

(1)主庫：

建立測試資料庫、測試表，並插入資料：

mysql> create database wjqtest;

Query OK, 1 row affected (0.02 sec)

mysql> use wjqtest;

Database changed

mysql>

mysql> create table tb_test(id int,name varchar(20));

Query OK, 0 rows affected (0.27 sec)

:mysql> insert into tb_test values(1,'wjq');

Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_test;

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

| id | name |

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

| 1 | wjq |

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

1 row in set (0.00 sec)

(2)從庫：

在從庫上檢視測試資料庫和測試表的同步效果

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| test |

| wjqtest |

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

5 rows in set (0.03 sec)

mysql> use wjqtest;

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> show tables;

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

| Tables_in_wjqtest |

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

| tb_test |

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

1 row in set (0.00 sec)

mysql> select * from tb_test;

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

| id | name |

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

| 1 | wjq |

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

1 row in set (0.00 sec)

到此為止，mysql主從複製環境部署完成！

作者：SEian.G（苦練七十二變，笑對八十一難）

MySQL主從複製環境部署

相關文章