Mysql

---

Mysql

Mysql的主从复制

1、创建一个主服务器master

#编辑master的my.cnf文件
[root@localhost mysql]# vim /etc/my.cnf
 
#在[mysqld]中添加：
server-id=1
log_bin=master-bin
log_bin_index=master-bin.index
binlog_do_db=test
#备注：
#server-id 服务器唯一标识。
#log_bin 启动MySQL二进制日志，即数据同步语句，从数据库会一条一条的执行这些语句。
#binlog_do_db 指定记录二进制日志的数据库，即需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可。
#binlog_ignore_db 指定不记录二进制日志的数据库，即不需要复制的数据库名，如果有多个数据库，重复设置这个选项即可。
#其中需要注意的是，binlog_do_db和binlog_ignore_db为互斥选项，一般只需要一个即可。

2、创建slave的用户和权限

#进入mysql数据库
[root@localhost mysql]# mysql -uroot -p
Enter password:
 
#创建从数据库的masterbackup用户和权限
mysql> grant replication slave on *.* to masterbackup@'192.168.17.%' identified by '123456';
#备注
#192.168.17.%通配符，表示0-255的IP都可访问主服务器，正式环境请配置指定从服务器IP
#若将 192.168.17.% 改为 %，则任何ip均可作为其从数据库来访问主服务器
 
#退出mysql
mysql> exit;

3、重启mysql服务
4、 查看主服务器状态

#进入mysql数据库
[root@localhost mysql]# mysql -uroot -p
Enter password:
 
#查看主服务器状态
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      154 | test         |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

5、创建一个从服务器slave

#编辑slave的my.cnf文件
[root@localhost mysql]# vim /etc/my.cnf
 
#在[mysqld]中添加：
server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
#replicate-do-db=test
#备注：
#server-id 服务器唯一标识，如果有多个从服务器，每个服务器的server-id不能重复，跟IP一样是唯一标识，如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。
#relay-log 启动MySQL二进制日志，可以用来做数据备份和崩溃恢复，或主服务器挂掉了，将此从服务器作为其他从服务器的主服务器。
#replicate-do-db 指定同步的数据库，如果复制多个数据库，重复设置这个选项即可。若在master端不指定binlog-do-db，则在slave端可用replication-do-db来过滤。
#replicate-ignore-db 不需要同步的数据库，如果有多个数据库，重复设置这个选项即可。
#其中需要注意的是，replicate-do-db和replicate-ignore-db为互斥选项，一般只需要一个即可。

6、重启slave的mysql服务
7、连接master

#进入mysql数据库
[root@localhost mysql]# mysql -uroot -p
Enter password:
 
#连接master主服务器
mysql> change master to master_host='192.168.17.130',master_port=3306,master_user='masterbackup',master_password='123456',master_log_file='master-bin.000001',master_log_pos=154;
#备注：
#master_host对应主服务器的IP地址。
#master_port对应主服务器的端口。
#master_log_file对应show master status显示的File列：master-bin.000001。
#master_log_pos对应show master status显示的Position列：154。

8、启动slave服务

#启动slave数据同步
mysql> start slave;
#停止slave数据同步（若有需要）
mysql> stop slave;

9、查看slave信息

mysql> show slave status\G;

Slave_IO_Running
和
Slave_SQL_Running 都为yes，则表示同步成功。

保证Mysql的主从一致

1、slave的i/o进程连接上master，并请求指定日志文件的指定位置之后的日志。

2、master接收到slave的请求后，通过io进程根据请求信息读取指定位置之后的日志信息，返回给slave的io进程。返回的信息除了日志信息外，还有master端的bin-log文件名称和位置

3、slave的io进程接收到信息后，将接收的日志信息依次添加到slave端的relay-log文件末端，并将接收到的master端的bin-log文件名称和位置记录到master-info中，以便下次读取的时候告诉master接收从哪里开始的日志

4、slave的Sql进程监测到relay-log中新增的内容，会解析relay-log的内容，并执行相应的sql操作

Mysql的事务隔离级别

1、READ_UNCOMMITTED（未提交读）:最低的隔离级别，允许读取尚未提交的数据，可能会导致脏读、不可重复度、幻读
2、READ_COMMITTED（提交读）:允许读取并发事务已提交的数据，可以阻止脏读，可能导致不可重复度、幻读
3、REPEATABLE_READ（可重复读）：对同一记录的多次读取结果是一样的，除非数据被本身事务所修改，可以防止脏读和不可重复读，可能导致幻读
4、SERIALIZABLE（串行化）: 最高的隔离级别，受到完全服从ACID原则，所有事务依次执行，这样的事务完全不会干扰，该级别可以防止脏读、不可重复读以及幻读。但是会影响程序的性能，通常不会使用该级别

mysql默认的事务隔离级别是REPEATABLE_READ（可重复读）；

脏读、不可重复读和幻读

• 脏读：
  表示一个事务能够读取另一个事务中还未提交的数据。例：事务A可以读取到事务B的操作数据的结果，但是事务B还没有提交事务

• 不可重复读：
  指在一个事务中，多次读取统一数据，数据不一致。例：事务A读取一条记录，同时事务B去修改这条记录，然后提交，事务A又去读取这条记录，发现和第一次得到的数据不一样了

• 幻读：
  指在一个事务中，多次按条件查询，得到的记录结果不一致。例：事务A第一次按条件查询得到了n条记录，同时事务B去给这个条件新增了一条记录，事务A再去按条件查询，得到了n+1条记录。
  发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

什么情况下索引会失效

1、索引列参与表达式计算：

SELECT name FROM user WHERE 'age' + 10 = 30;

2、%–模糊查询：

SELECT * FROM user WHERE name LIKE '码农%' -- 走索引 
SELECT * FROM user WHERE name LIKE '%码农%' -- 不走索引

3、字符串与数字比较不走索引：

SELECT * FROM user WHERE name="1" — 走索引
SELECT * FROM user WHERE name= 1  — 不走索引，同样也是使用了函数运算

4、查询条件中有 or ，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引：

select * from user where name='xxx' or age='xx' or role= 45;

5、正则表达式不使用索引

sql优化

1、选取最适用的字段属性

• 数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
• 例如：对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。

2、使用连接（JOIN）来代替子查询(Sub-Queries)

3、使用联合(UNION)来代替手动创建的临时表

4、使用索引

5、explain查看执行计划，优化sql