MySQL主从复制和读写分离

数据库的拆分

分库分表：属于数据库的一种数据拆分。

单表数据过大进行拆分：考虑大数据过大和磁盘读取速度问题，首先应该进行分库。

如500G在一个库：

1、分库（分到两个MySQL数据库中）

1. 垂直分库：按照业务拆分

2. 水平分库：统一业务的数据进行拆分（把同一个表的数据进行拆分成两张以上的表）

MySQL主动复制

数据库也有可能出现问题，所以需要数据自动备份。MySQL提供了主从备份功能。

MySQL的权限

数据库是安全性比较高的存储设备，权限管理是数据库中非常重要的一块。以下是数据库的所有权限：

权限	权限级别	权限说明
CREATE	数据库、表、索引	创建数据库、表或索引权限
DROP	数据库、表	删除数据库或表权限
GRANT OPTION	数据库、表	赋予权限选项
REFERENCES	数据库或表
ALTER	表	更改表，比如添加字段、索引等
DELETE	表	删除数据权限
INDEX	表	索引权限
INSERT	表	插入权限
SELECT	表	查询权限
UPDATE	表	更新权限
CREATE VIEW	视图	创建视图权限
SHOW VIEW	视图	查看视图权限
ALTER ROUTINE	存储过程	更改存储过程权限
CREATE ROUTINE	存储过程	创建存储过程权限
EXECUTE	存储过程	执行存储过程权限
FILE	服务器主机上的文件访问	文件访问权限
CREATE TEMPORARY TABLES	服务器管理	创建临时表权限
LOCK TABLES	服务器管理	锁表权限
CREATE USER	服务器管理	创建用户权限
PROCESS	服务器管理	查看进程权限
RELOAD	服务器管理	执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
REPLICATION CLIENT	服务器管理	复制权限
REPLICATION SLAVE	服务器管理	复制权限
SHOW DATABASES	服务器管理	查看数据库权限
SHUTDOWN	服务器管理	关闭数据库权限
SUPER	服务器管理	执行kill线程权限
all privileges	所有权限	拥有所有权限

授权语句

Grant 权限1，权限2，权限3 ON 数据库.表 TO 用户@‘IP地址’ IDENTIFIED BY ‘密码’ 。

如：
授权用户删除表的权限：
Grant DROP TO zhangsan@’%’ IDENTIFIED BY ‘123456’.

一、主从复制（分区）

故名思意，至少需要两台MySQL服务器，一台主（写）服务器（Master），一台从（读）服务器（Slave）。写在主服务器上的数据会自动同步到从服务器上。（windows作为从服务器、虚拟机linux作为主服务器）

ysql_master_slave.pn

1.1、在主服务器上创建一个能远程访问的账户

从服务器同步主服务器的数据，需要登录主服务器。因此，需要一个账户用来做主从负责的工作。

GRANT REPLICATION SLAVE,RELOAD,SUPER ON . TO root@’%’ IDENTIFIED BY ‘123456’;
%是表示匹配所有IP地址

其中192.168.72.188是从服务器的IP地址。“123456”是登录密码

REPLICATION SLAVE：复制权限

RELOAD：刷新配置和重新加载配置的权限

SUPER：kill线程的权限。

1.2、配置Master

1. 在/etc/文件夹下，进入my.cnf文件，
2. 在配置文件[mysqld]加入如下值：
   server-id=1 //主服务器的id值
   log-bin=mysql-bin //二进制变更日值

以上配置为开启MySQL服务器的log-bin日志记录。比如：

[mysqld]
server-id=1 //服务器ID
log-bin=mysql-bin //开启日志记录
binlog-do-db=java1706 //只同步配置的这个数据库

1.3、重启Master服务器

service mysql restart;

重启完成之后，测试是否配置成功，执行如下命令：

mysql> SHOW MASTER STATUS
ysql_master_slave2.pn

1.4、配置Slave

在etc文件下进入my.cnf文件加上server-id= 131（如下图：下图是在windows环境下的my.ini配置）

ysql_master_slave3.pn

windows下的my.ini在MySQL安装目录下（比如：C:\ProgramData（隐藏文件夹）\MySQL\MySQL Server 5.7\my.ini）。 如果是Linux服务器下的mysql配置，测同Master配置路径。

1.5、重启Slave

1. linux:
   service mysql restart

2. windows:
   右键选择MySQL启动图标，选择重启。

   进入计算机管理中的“服务”，选择mysql服务，右键关闭和开启

1.6、slave连接到Master

change master to master_host=‘主服务器ip’,master_user=‘root’,master_password=‘123456’;（连接主服务器）
然后查看是否连通：SHOW SLAVE STATUS。
ysql_master_slave
Slave_IO_Running, 和Slave_SQL_Running是No表明slave还没有开始复制过程。
日志的位置为4而不是0，这是因为0只是日志文件的开始位置，并不是日志位置。（等于日志前有空格，日志没有顶格输入）。

1.7、开始主从复制

start slave
之后可以再看结果：SHOW SLAVE STATUS.

如果结果不是两个yes，执行SHOW SLAVE STATUS命令后，在表中有一个log字段描述了错误原因;
ysql_master_slave

Slave_IO_Running, 和Slave_SQL_Running是yes则表示成功。