一、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)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| 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)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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（苦练七十二变，笑对八十一难）