mysql主从复制

1.MySQL-master:192.168.183.201

   mysql-slave:192.168.182.202

  使用yum方式安装MySQL，并使用service mysqld start启动MySQL数据库；

  使用mysqladmin -uroot -p password 123 修改登录密码，默认为空；

2.mysql-master

mysql> create database temp;

Query OK, 1 row affected (0.00 sec)

mysql> use temp;

Database changed

mysql> create table users(

    -> user_name char(16) not null,

    -> user_passwd char(48) default "",

    -> primary key (user_name));

Query OK, 0 rows affected (0.00 sec)

 

mysql> insert into users values("zhangsan","123");

Query OK, 1 row affected (0.00 sec)

 

mysql> select * from users;

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

| user_name | user_passwd |

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

| zhangsan  | 123         |

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

1 row in set (0.00 sec)

 

mysql> show variables like 'log_bin';

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

| Variable_name | Value |

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

| log_bin       | OFF   |

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

1 row in set (0.00 sec)

 

mysql> quit;

Bye

[root@mysql-master ~]# cp /usr/share/doc/mysql-server-5.1.73/my-medium.cnf /etc/my.cnf

cp：是否覆盖"/etc/my.cnf"？ y

[root@mysql-master ~]# vi /etc/my.cnf

[root@mysql-master ~]# service mysqld restart

停止 mysqld：                                              [确定]

正在启动 mysqld：                                          [确定]

[root@mysql-master ~]# mysql -uroot -p123

 

mysql> show variables like 'log_bin';

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

| Variable_name | Value |

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

| log_bin       | ON    |

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

1 row in set (0.00 sec)

 

mysql> show master status; （用于配置slave节点）

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 |      106 |              |                  |

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

1 row in set (0.00 sec)

 //为slave赋予权限

mysql> grant replication slave on *.* to rep@'192.168.183.%' identified by'123';

Query OK, 0 rows affected (0.00 sec)

 //锁表

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.00 sec)

//备份表

[root@mysql-master ~]# mysqldump -u root -p123 --all-databases | gzip > /root/database_`date '+%m-%d-%Y'`.sql.gz

[root@mysql-master ~]# scp database_07-27-2017.sql.gz root@192.168.183.202:/root

 3.mysql-slave

root@mysql-slave ~]# cp  /usr/share/doc/mysql-server-5.1.73/my-medium.cnf /etc/my.cnf

cp：是否覆盖"/etc/my.cnf"？ y

[root@mysql-slave ~]# vi /etc/my.cnf

[root@mysql-slave ~]# service mysqld restart

停止 mysqld：                                              [确定]

正在启动 mysqld：                                          [确定]

[root@mysql-slave ~]# mysql -uroot -p123

mysql> show variables like 'log_bin';

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

| Variable_name | Value |

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

| log_bin       | OFF   |

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

1 row in set (0.00 sec)

 

mysql> show variables like 'server_id';

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

| Variable_name | Value |

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

| server_id     | 2     |

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

1 row in set (0.00 sec)

 

[root@mysql-slave ~]# gzip -d database_07-27-2017.sql.gz

[root@mysql-slave ~]# mysql -uroot -p <database_07-27-2017.sql

Enter password:

 

mysql> show databases;

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

| Database           |

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

| information_schema |

| mysql              |

| temp               |

| test               |

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

4 rows in set (0.00 sec)

 

mysql> CHANGE MASTER TO MASTER_HOST='192.168.183.201', MASTER_USER='rep', MASTER_PASSWORD='123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;

Query OK, 0 rows affected (0.03 sec)

 

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show slave status \G;

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.183.201

                  Master_User: rep

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 253

               Relay_Log_File: mysqld-relay-bin.000002

                Relay_Log_Pos: 398

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

              Relay_Log_Space: 554

              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:

1 row in set (0.00 sec)

4.测试

mysql-master：

mysql> use temp;

Database changed

mysql> insert into users values("lisi","123");

Query OK, 1 row affected (0.00 sec)

mysql-slave：

mysql> use temp;

Database changed

mysql> select * from users;

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

| user_name | user_passwd |

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

| zhangsan  | 123         |

| lisi      | 123         |

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

2 rows in set (0.00 sec)