Mysql原理及主从架构

Mysql的逻辑结构：

第一层：连接管理层 用户连接的认证，管理、安全等

第二层：核心部分。包括查询解析、分析、优化、缓存。存储过程、触发器、试图都在这个层次上实现管理

第三层：存储引擎层

补充：parses 解析，Mysql 对任何一个查询都会做解析；每一次sql查询请求，mysql先去检查缓存，若果没有缓存的话在做词法分析

存储引擎层主要通过storage API 与核心层连接起来

并发控制：运用读锁和写锁

 事务：经过ACID（原子性、一致性、隔离性、持久性）测试，符合的才是事务

 

Mysql 的存储引擎

Mysql的最大的工作特征：插件式的存储引擎。不同的存储引擎所使用的管理模块是不同的，备份和存储的方式也不同。引擎并不是管理数据库本身。而是管理数据库运行时的进程。作为公司来言可以开发自己的存储引擎。

   默认的myisam    不支持事务，不支持行及锁，不支持外键 ，支持表压缩、支持在线备份 

       参数 ：key_buffer_size  设定存储myisam索引的缓冲大小。不能缓存数据，数据考操作系统缓存，默认8M大小，最大4G。

            Concurrent_insert 是否支持并发插入的，默认打开。0表示不允许、1表示允许并发写入但是不允许在行之间产生空隙，2允许产生空闲数据块。

（这些都在my.cnf中的mysqld段中写）

默认的支持事务的引擎(也是运用最广泛的)inodb

Memery : 类似一个临时的数据库，把数据可的表和更重信息建构在内存中，实现快速查询。

Merger 和myisam 结合起来的常用引擎，主要实现表的partion的

InoDB: 主要用来处理事务的，表存储在表空间中的；索引可提供非常快速的逐渐查找，行级别锁

NDB  主要提供NDB cluster ，提供集群的管理。

 

 

修改密码的三种方法：

1. 在shell  下    mysqladmin  -u user_name   -h  ip_addr  password ‘your_password’

2. mysql> set password for ‘username’@’hostname’=password(‘your_password’);

3. mysql> update user set password=PASSWORD(‘123456’) where USER=’username’ and HOST=’host_name’;

 

 

主从配置：

初始配置主从服务器方法
配置说明
这里所说的"初始配置主从服务器"指的是，已经安装好了MySQL数据库，第一次配置主从复制功能，主数据库中还没有历史数据，因此，如果 MySQL的数据目录下已经有多余的文件，比如二进制日志文件mysql- bin.*，master.info，relay-bin.*，relay-log.*，请先删除。
主从复制配置方法
创建复制帐号
在主服务器上创建复制帐号，授予相应的权限。注意，复制帐号的口令最好不要超过6位，而且不要带"#"等特殊字符。创建命令如下：

GRANT REPLICATION SLAVE ON *.* TO ‘帐号名称’@’%’ IDENTIFIED BY ‘口令’

比如：
　　　 GRANT REPLICATION SLAVE ON *.* TO ‘repuser’@‘%’ IDENTIFIED BY ‘111111’

在主服务器的MySQL配置文件中添加选项，打开二进制日志记录功能
server-id = 16204
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

　各个配置选项的说明如下：
server-id = 16204
服务器ID号，整数值，保证唯一标识一台服务器就可以
log-bin=mysql-bin
打开二进制日志
binlog-ignore-db=mysql
忽略mysql数据库复制
binlog-ignore-db=information_schema
忽略information_schema数据库复制

在从服务器的MySQL配置文件中添加相关复制选项
server-id =16214
master-host=172.20.16.204 
master-user= repuser
master-password=111111
relay-log=relay-bin
relay-log-index=relay-bin
replicate-ignore-db=mysql
replicate-ignore-db=information_schema

各个配置选项的说明如下：
server-id =16214
　 服务器ID号，整数值，保证唯一标识一台服务器比如：16214
master-host=172.20.16.204
主服务器IP地址，比如：172.20.16.204
master-user= repuser
　主服务器上创建的复制用户帐号名称，比如以上创建的：repuser
master-password=111111
　 主服务器上创建的复制用户帐号密码，比如以上创建的：111111
relay-log=relay-bin
　 中继日志名称
relay-log-index=relay-bin
　 中继索引文件名称
replicate-ignore-db=mysql
忽略mysql数据库复制
replicate-ignore-db=information_schema
　 忽略information_schema数据库复制

重启服务器
先重启主服务器，起来后再重启从服务器

察看运行状态
在主服务器上执行命令：show master status\G 
在从服务器上执行命令：show slave status\G

　如果复制状态Slave_IO_Running，Slave_SQL_Running都为Yes，说明复制已经配置正确
 Slave_IO_Running: Yes
　　　　 Slave_SQL_Running: Yes

向已有主服务器配置一台新的从服务器
配置说明
这里所说的"从已有主服务器配置一台新的从服务器"指的是，主从服务器都已经安装好了MySQL数据库，主服务器已经在运行，需要增加一个新的从服务器，已经有很多历史数据需要复制到从服务器上。征对这样的应用场景，请按下列方法配置复制。

主从配置方法
打开主服务器二进制日志功能
如果主服务器的二进制日志记录功能没有打开，在MySQL配置参数文件（比如/etc/my.cnf）中添加选项，重启MySQL服务，打开二进制日志功能。
 server-id = 16204
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=information_schema

　各个配置选项的说明如下：
server-id = 16204
服务器ID号，整数值，保证唯一标识一台服务器就可以
log-bin=mysql-bin
打开二进制日志
binlog-ignore-db=mysql
忽略mysql数据库复制
binlog-ignore-db=information_schema
忽略information_schema数据库复制

创建复制帐号
在主服务器上创建复制帐号，授予相应的权限。注意，复制帐号的口令最好不要超过6位，而且不要带"#"等特殊字符。创建命令如下：

GRANT REPLICATION SLAVE ON *.* TO ‘帐号名称’@’%’ IDENTIFIED BY ‘口令’

比如：
　　　 GRANT REPLICATION SLAVE ON *.* TO ‘repuser’@’%’ IDENTIFIED BY ‘111111’

备份主服务器的数据文件
备份主服务器的数据文件
执行以下命令，给主服务器所有表加锁，禁止继续写入数据：
　　　　FLUSH TABLES WITH READ LOCK；

记录复制启动断点
执行以下命令，记录从服务器开始启动复制的断点，包括日志名和偏移量。
　 mysql>SHOW MASTER STATUS；
比如：
 mysql> show master status\G
　　　　 file: mysql-bin.000025
　　　　 Position: 13729
　　　　 Binlog_Do_DB:
Binlog_Ignore_DB: mysql,information_schema,mysql,information_schema

日志文件名是：mysql-bin.000025
偏移量是：13729
　 　　　 注意，从服务器将从这个位置开始复制。

-----------一下部分本人未作验证，mysiam可以直接复制表，innodb的处理有所不同----

打包备份主服务器数据文件
打包需要复制的数据库文件，如果数据库文件安装在/var/lib/mysql目录下，可以执行命令：
tar zcf /tmp/backup.tar.gz /var/lib/mysql

该命令将把/var/lib/mysql目录下所有的文件都打包，也包括MySQL的系统表，而系统表一般都是不需要同步到从服务器上的，所以最好选择你需要复制的数据库目录，进行打包就可以了，如需要复制两个数据库（PRIVATE_DB1 ,PRIVATE_DB2）到从服务器上，只要把这两个目录打包：
tar czf /tmp/backup.tar.gz PRIVATE_DB1 PRIVATE_DB2

主服务器表解锁
执行以下命令，解开主服务器的读锁，主服务器可以继续更新数据：
mysql> UNLOCK TABLES；

在从服务器上恢复主备份文件
在从服务器相应的数据库目录（比如：/var/lib/mysql）上恢复主服务器上备份的数据库文件，恢复时需要关闭从服务器的MySQL服务，比如：
Cd /var/lib/mysql
Tar xzf backup.tar.gz

启动从服务器，设置复制断点
在MySQL中执行以下命令，设置复制开始断点：
change master to master_user=‘repuser’,
 master_password=‘654321’ , master_host=‘172.20.16.204’,
　　　　 master_log_file=‘ mysql-bin.000025’,master_log_pos=13729;

　　表示从日志文件mysql-bin.000025’，位置13729开始复制

察看复制状态
在从服务器上执行命令：show slave status\G

　如果复制状态Slave_IO_Running，Slave_SQL_Running都为Yes，说明复制已经配置正确

 Slave_IO_Running: Yes
　　　　 Slave_SQL_Running: Yes