mysql主从数据库设计

简要：（一主一从）一主多从同理

1、主数据库的my.cnf (/etc/my.cnf)中：

[mysqld]

# 开启二进制日志功能，可以随便取（关键）

log-bin = mysql-bin

# 同一局域网内注意要唯一
server-id = 1

从数据库的my.cnf (/etc/my.cnf)中（多台数据库重复此操作）：

log-bin=mysql-bin
server-id = 22

2、主数据库创建用户：

mysql>  grant replication slave on *.* to 'hja'@'%' identified by 'hja100521';
Query OK, 0 rows affected, 1 warning (0.07 sec)

主数据库的信息：

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

从数据库相关配置（多台数据库重复此操作）：

mysql> change master to master_host='10.0.0.3',master_user='hja' ,master_password='hja100521',master_log_file='mysql-bin.000003',
    -> master_log_pos=848;
Query OK, 0 rows affected, 2 warnings (0.11 sec)

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

查看信息

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.3
                  Master_User: hja
          ........................................................
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes（均为yes表示设置成功）
          ......................................................

---------------------------------------------------------------------如果不报错，跳过此步-------------------------------------------------------

注意：

错误一：如果Slave_IO_Running: No ;   Slave_SQL_Running: Yes

解决步骤：

重启master库：service mysqld restart

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000002 |       298 |              |                  | 

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

mysql> stop slave ;

mysql> change master to Master_Log_File='mysql-bin.000002',Master_Log_Pos=298;

mysql> start slave;

mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.3
                  Master_User: hja
          ........................................................
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes（均为yes表示设置成功）
          ......................................................

错误二：如果Slave_IO_Running: Yes ;   Slave_SQL_Running:No

mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;

mysql> show slave status\G;

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.0.0.3
                  Master_User: hja
          ........................................................
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes（均为yes表示设置成功）
          ......................................................

-----------------------------------------------------------报错部分结束------------------------------------------------------------------

测试：

主数据库创建数据库、数据表，（从数据库不需操作，主会自动将其复制到从数据库）

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

mysql> use test;
Database changed
mysql> create table user(id int(10) auto_increment primary key,name varchar(30)) engine=innodb,default charset = utf8;
Query OK, 0 rows affected (0.10 sec)

主数据库添加一条数据：

mysql> insert into user(name) values('zhangsan');
Query OK, 1 row affected (1.72 sec)

从数据库查询：

mysql> select * from user;
+----+------------+
| id | name       |
+----+------------+
|  1 | zhangsan |
+----+------------+
1 row in set (1.02 sec)

至此，实验成功，结束