数据库主从配置方法

数据库主从配置
对主数据库锁表并备份
主库需要开启binlog日志
更改从库/etc/my.cnf中[mysqld]模块中的#log_bin=PATH(binlog日志存储的位置）

锁表：
root@leadchina 09:13:18->flush table with read lock;
查看主库状态
root@leadchina 09:15:16->show master status;
+--------------------+----------+--------------+------------------+-------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------------+----------+--------------+------------------+-------------------+
| mysqlbinlog.000001 |     2807 |              |                  |                   |
+--------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
​
备份数据库
[root@localhost /]# mysqldump -uroot -pleadchina -A -B --events >/beifen.sql
对库解锁
root@leadchina 09:27:58->unlock tables;
Query OK, 0 rows affected (0.00 sec)
对从库导入数据库并创开启Slave
更改从库/etc/my.cnf中[mysqld]模块中的server-id，这个值必须要大于主库的值

从主库中将数据复制到本机
[root@localhost ~]# scp root@10.30.162.161:/beifen.sql /
将数据导入从库
[root@localhost ~]# mysql -uroot -pleadchina -A </beifen.sql 
查看数据是否已经导入
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| School             |
| data               |
| lead               |
| mail               |
| mount_file         |
| mysql              |
| mysql_binlogs      |
| performance_schema |
| sys                |
| test               |
+--------------------+
11 rows in set (0.00 sec)
​
创建从库
CHANGE MASTER TO 
    MASTER_HOST='10.30.162.161',                                    ***主库地址
    MASTER_PORT=3306,                                               ***主库端口
    MASTER_USER='root',                                             ***主库账号
    MASTER_PASSWORD='leadchina',                                    ***主库密码
    MASTER_LOG_FILE='mysqlbinlog.000001',                             ***主库binlog名称
    MASTER_LOG_POS=2807;                                            ***主库binlog位置                           
查看relay-log-info
[root@localhost mysql]# cat relay-log.info 
7
./localhost-relay-bin.000002
322
mysqlbinlog.000001
2807
0
0
1

从库开启Slave
start Slave;
查看Slave状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.30.162.161
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqlbinlog.000001
          Read_Master_Log_Pos: 2807
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 322
        Relay_Master_Log_File: mysqlbinlog.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

查看主库线程状态
show processlist\G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    6
Current database: *** NONE ***
​
*************************** 1. row ***************************
     Id: 4
   User: root
   Host: 10.30.162.162:53696
     db: NULL
Command: Binlog Dump
   Time: 1225
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL
*************************** 2. row ***************************
     Id: 6
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
2 rows in set (0.00 sec)
​
###### 查看从库的线程状态

mysql> show processlist\G
*************************** 1. row ***************************
     Id: 8
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 2. row ***************************
     Id: 41
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 4171
  State: Waiting for master to send event
   Info: NULL
*************************** 3. row ***************************
     Id: 42
   User: system user
   Host: 
     db: NULL
Command: Connect
   Time: 4474
  State: Slave has read all relay log; waiting for more updates
   Info: NULL
3 rows in set (0.01 sec)
​