mysql主从复制

一：原理

二：配置

主：172.25.67.2 server2 master
从：172.25.67.3 server3 slave

1：安装mysql

mysql-devel 开发用到的库以及包含文件
mysql mysql 客户端
mysql-server 数据库服务器

主：

[root@server2 tmp]#  yum install -y mysql*  

从：

[root@server3 ～]#  yum install -y mysql*  

修改配置文件

[root@server2 tmp]# vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql      # 从给定目录读取数据库文件
socket=/var/lib/mysql/mysql.sock  # 为MySQL客户程序与服务器之间的本地通信指定一个套接字文件(Linux下默认是/var/lib/mysql/mysql.sock文件)
user=mysql
log-bin= mysql-binlog      #[必须加 ]启用二进制日志，这个mysql-bin也可以自定义，这里也可以加上路径，如：/home/www/mysql_bin_log/mysql-bin
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=2    #mysql的同步的数据中是包含server-id的，用于标识该语句最初是从哪个server写入的，所以server-id一定要有的，一般就写，ip地址的最后一位，比如172.25.67.2，就写2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

从：

[root@server3 mysql]# vim /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
read_only=1
server-id=3
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

3：启动服务

[root@server3 mysql]# /etc/init.d/mysqld start
正在启动 mysqld：                                          [确定]

4.设置数据库

两边初始化mysql 设置密码

[root@server1 ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):<–初次运行直接回车
OK, successfully used password, moving on…
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
New password: <– 设置root用户的密码
Re-enter new password: <– 再输入一次你设置的密码
Password updated successfully!
Reloading privilege tables..
… Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] <– 是否删除匿名用户,生产环境建议删除，所以直接回车
… Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] <–是否禁止root远程登录,根据自己的需求选择Y/n并回车,建议禁止
… Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] <– 是否删除test数据库,直接回车
- Dropping test database…
… Success!
- Removing privileges on test database…
… Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] <– 是否重新加载权限表，直接回车
… Success!
Cleaning up…
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!

主：

创建帐号（slave来复制时用的身份）

[root@server2 tmp]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.71-log Source distribution

Copyright (c) 2000, 2013, 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> GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by 'redhat';
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000001 |      250 |              |                  |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

从：

登录操作

[root@server3 mysql]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, 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='172.25.67.2', MASTER_USER='backup',master_password='redhat', master_log_file='mysql-binlog.000001',master_log_pos=250;
Query OK, 0 rows affected (0.73 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: 172.25.67.2
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-binlog.000001
          Read_Master_Log_Pos: 250
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 254
        Relay_Master_Log_File: mysql-binlog.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: 250
              Relay_Log_Space: 410
              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)

ERROR: 
No query specified

5：测试

主：

mysql> create database zpy;
Query OK, 1 row affected (0.00 sec)

mysql> use zpy;
Database changed
mysql> create table zpy(id int(3),name char(10));
Query OK, 0 rows affected (0.21 sec)

mysql> show tables;
+---------------+
| Tables_in_zpy |
+---------------+
| zpy           |
+---------------+
1 row in set (0.00 sec)

mysql> select * from zpy;
Empty set (0.00 sec)

mysql> insert into zpy values(001,"kaikai");
Query OK, 1 row affected (0.00 sec)

mysql> select * from zpy;
+------+--------+
| id   | name   |
+------+--------+
|    1 | kaikai |
+------+--------+
1 row in set (0.00 sec)

mysql> Ctrl-C -- exit!
Aborted

从：

直接查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| zpy                |
+--------------------+
3 rows in set (0.00 sec)

mysql> use zpy;
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_zpy |
+---------------+
| zpy           |
+---------------+
1 row in set (0.00 sec)

mysql> select * from zpy;
+------+--------+
| id   | name   |
+------+--------+
|    1 | kaikai |
+------+--------+
1 row in set (0.00 sec)

mysql> importantCtrl-C -- exit!
Aborted
![这里写图片描述](https://img-blog.csdn.net/20171014224618126?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvemVyb3podXhpYW96aHU=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)