mysql主从同步

数据库类型	IP	有无数据
主数据库	192.168.89.135	有
从数据库	192.168.89.129	五

1.mysql主从配置

1.1 主数据库配置

1.1.1 主数据库修改配置文件

[root@localhost ~]# vim /etc/my.cnf

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin=mysql-bin       启用binlog日志
server-id=1             数据库服务器唯一标识符，主库和从库不能一致且主库必须必从库大
[root@localhost ~]# service mysqld restart   重启服务
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

1.1.2 创建同步用的用户账号

[root@localhost ~]# mysql -uroot -p
mysql> create user 'kong'@'192.168.89.129' identified by 'bai123';            创建同步账号并授权，设置密码为：bai123 ，IP地址设置为从服务器的IP地址
Query OK, 0 rows affected (0.01 sec)

mysql> grant replication slave on *.* to 'kong'@'192.168.89.129';     授权给从库使用
Query OK, 0 rows affected (0.10 sec)

mysql> flush privileges;         
Query OK, 0 rows affected (0.01 sec)

mysql> quit 
Bye
[root@localhost ~]# mysqldump -uroot -p --all-databases > /root/alldb.sql        导出数据库数据
Enter password:  
[root@localhost ~]# ls
alldb.sql  anaconda-ks.cfg  initial-setup-ks.cfg

1.1.3 查看主库状态

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

1.2 从数据库配置

1.2.1 将刚刚主数据库导出文件导入从数据库并修改匹配文件

[root@localhost ~]# mysql -uroot -p < alldb.sql 
Enter password:
[root@localhost ~]# vim /etc/my.cnf

[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id=2          数据库服务器唯一标识符，必须唯一
relay-log=mysql-relay-bin
[root@localhost ~]# service mysqld restart   重启服务
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

1.2.2 配置并启动主从复制

[root@localhost ~]# mysql -uroot -p
Enter password: 
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.89.135',                       主库IP地址
    -> MASTER_USER='kong',                                 主库创建的账户用户名
    -> MASTER_PASSWORD='bai123',                           主库创建的账户密码
    -> MASTER_LOG_FILE='mysql-bin.000001',                 主库表，通过show master status;在主库查看
    -> MASTER_LOG_POS=621;
Query OK, 0 rows affected, 2 warnings (0.37 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

1.2.3 查看从服务器状态

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.89.135
                  Master_User: kong
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 773
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 472
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes                           
            Slave_SQL_Running: Yes                 Slave_IO和 Slave_SQL为yes即可
            ···

1.3 查看效果

1.3.1 在主库中创建一个新库并新建一个表

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> CREATE DATABASE IF NOT EXISTS student;
Query OK, 1 row affected (0.01 sec)

mysql> use student;
Database changed

mysql> create table xxyy (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);
Query OK, 0 rows affected (0.14 sec)

mysql> insert into xxyy values (1,'sean',20),(2,'tom',23),(3,'jerry',30);
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from xxyy;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | sean  |   20 |
|  2 | tom   |   23 |
|  3 | jerry |   30 |
+----+-------+------+
3 rows in set (0.00 sec)

1.3.2 从数据库查看是否同步

mysql> show databases;                    主数据库还未创建库和表
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> show databases;                  主数据库还创建了库和表
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use student;
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> select * from xxyy;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | sean  |   20 |
|  2 | tom   |   23 |
|  3 | jerry |   30 |
+----+-------+------+
3 rows in set (0.01 sec