Mysql 数据库必备操作
MySQL 数据库安装完毕之后,对 MYSQL 数据库中各种指令的操作变得尤为重要,熟练掌握 MYSQL 必备命令是 SA、DBA 必备工作之一,如下为 MYSQL 数据库中操作必备命令,所有操作指令均在 MYSQL 命令行中操作,不能在 Linux Shell 解释器上直接运行:
mysql 或者/usr/local/mysql-5.7.29/bin/mysql,按 Enter 键,进入 MYSQL 命令行界面如图
Mysql 数据库必备命令
命令 | 说明 |
---|---|
show databases; | 查看所有的数据库; |
create database jfedu; | 创建名为 jfedu 数据库; |
use jfedu; | 进入 jfedu 数据库; |
show tables; | 查看数据库里有多少张表; |
create table t1 (id varchar(20),name varchar(20)); | 创建名为 t1 表,并创建两个字段,id、name,varchar 表示设置数据长度,用字符来定义长度单位,其中 1 汉字=2 字符=2Bytes; |
insert into t1 values (“1”,“jfedu”); | 向表中插入数据; |
select * from t1; | 查看 t1 表数据内容; |
Select * from t1 where id=1 and age =’jfedu’; | id、age 多个条件查询; |
desc t1; | 查看 t1 表字段内容; |
alter table t1 modify column name varchar(20); | 修改 name 字段的长度; |
update t1 set name=‘jfedu.net’ where id=1; | 修改 name 字段的内容; |
flush privileges; | 刷新权限; |
delete from t1 ; | 清空表内容; |
drop table t1 ; | 删除表; |
drop database jfedu; | 删除 jfedu 数据库; |
show variables like ‘%char%’; | 查看数据库字符集; |
show engines; | 查看 MySQL 存储引擎; |
show variables like ‘%storage_engine%’; | 查看 MySQL 默认的存储引擎; |
alter table t1 engine=innodb; | 修改 MySQL t1 表存储引擎; |
GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘123456’ WITH GRANT OPTION; | root使用123456从任何主机连接到mysql数据库 |
Mysql 数据库字符集设置(已经使用的库最好别动,找dba)
计算机中储存的信息都是用二进制数方式来表示的,读者每天看到屏幕显示的英文、汉字等字符是二进制数转换之后的结果。通俗的说,将汉字按照某种字符集编码存储在计算机中,称为"编码"。将存储在计算机中的二进制数解析显示出来,称为"解码",在解码过程中,如果使用了错误的解码规则,会导致显示乱码。MYSQL 数据库在存储数据时,默认编码 latin1,存储中文字符时,在显示或者 WEB调用时会显示为乱码,为解决该乱码问题,需修改 Mysql 默认字符集为 UTF-8,有两种方
法:
- 编辑 vim /etc/my.cnf 配置文件,在相应段中加入相应的参数字符集修改完毕,重启 MySQL 服务即可。
[client]字段里加入: default-character-set=utf8
[mysqld]字段里加入: character-set-server=utf8
[mysql]字段里加入 : default-character-set=utf8
- MYSQL 命令行中运行如下指令,如图
show variables like '%char%';
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
Mysql 数据库密码管理
MYSQL 数据库在使用过程中为了加强安全防范,需要设置密码访问,如何设置密码、及密码忘记如何破解呢,如下为设置密码授权、密码修改及密码破解的方法:
- MYSQL 创建用户及授权
grant all on jfedu.* to test@localhost identified by 'pas';
grant select,insert,update,delete on *.* to test@”%” identified by 'pas';
grant all on jfedu.* to test@’192.168.111.118’ identified by 'pas';
- MYSQL 密码破解方法
在使用 MYSQL 数据库中,偶尔会出现密码忘记,或者被其他人员修改掉数据库权限的,如果需要紧急修改密码,如何破解 MYSQL 密码呢,首先停止 MYSQL 数据库服务,以跳过权限方式启动,命令如下:
CentOS6.x
/etc/init.d/mysqld stop
/usr/bin/mysqld_safe --user=mysql --skip-grant-tables &
CentOS7.x
systemctl mysqld stop
/usr/bin/mysqld_safe --user=mysql --skip-grant-tables &
MYSQL 跳过权限方式启动后,在 Shell 终端执行 mysql 命令并按 Enter 键,进入 mysql命令行
由于 MYSQL 用户及密码认证信息存放在 mysql 库中的 user 表,需进入 mysql 库,更新相应的密码字段即可,例如将 MYSQL 中 root 用户的密码均改为 12345
use mysql
update user set password=password('123456') where user='root';
MYSQL root 密码修改完,需停止以 Mysql 跳过权限表的启动进程,再以正常方式启动 MYSQL,再次以新的密码登陆即可进入 Mysql 数据库
Mysql 数据库配置文件详解
理解 MYSQL 配置文件,可以更快的学习和掌握 MYSQL 数据库服务器,如下为 MYSQL配置文件常用参数详解:
命令 | 说明 |
---|---|
[mysqld] | //服务器端配置 |
datadir=/data/mysql | //数据目录 |
socket=/var/lib/mysql/mysql.sock | //socket 通信设置 |
user=mysql | //使用 mysql 用户启动; |
symbolic-links=0 | //是否支持快捷方式; |
log-bin=mysql-bin | //开启 bin-log 日志; |
server-id = 1 | //mysql 服务的 ID; |
auto_increment_offset=1 | //自增长字段从固定数开始; |
auto_increment_increment=2 | //自增长字段每次递增的量; |
socket = /tmp/mysql.sock | //为MySQL客户程序与服务器之间的本地通信套接字文件; |
port = 3306 | //指定 MsSQL 监听的端口; |
key_buffer = 384M | //key_buffer 是用于索引块的缓冲区大小; |
table_cache = 512 | //为所有线程打开表的数量; |
sort_buffer_size = 2M | //每个需要进行排序的线程分配该大小的一个缓冲区; |
read_buffer_size = 2M | //读查询操作所能使用的缓冲区大小。 |
query_cache_size = 32M | //指定 MySQL 查询结果缓冲区的大小 |
read_rnd_buffer_size = 8M | //改参数在使用行指针排序之后,随机读; |
myisam_sort_buffer_size = 64M | //MyISAM 表发生变化时重新排序所需的缓冲; |
thread_concurrency = 8 | //最大并发线程数,取值为服务器逻辑CPU 数量×2; |
thread_cache = 8 | //缓存可重用的线程数; |
skip-locking | //避免 MySQL 的外部锁定,减少出错几率增强稳定性。 |
default-storage-engine=INNODB | //设置 mysql 默认引擎为 Innodb; |
#mysqld_safe config | |
[mysqld_safe] | //mysql 服务安全启动配置; |
log-error=/var/log/mysqld.log | //mysql 错误日志路径; |
pid-file=/var/run/mysqld/mysqld.pid | //mysql PID 进程文件; |
key_buffer_size = 2048MB | //MyISAM 表索引缓冲区的大小; |
max_connections = 3000 | //mysql 最大连接数; |
innodb_buffer_pool_size = 2048MB | //InnoDB 内存缓冲数据和索引大小; |
basedir = /usr/local/mysql55/ | //数据库安装路径; |
[mysqldump] | //数据库导出段配置; |
max_allowed_packet =16M | //服务器和客户端发送的最大数据包; |
MySQL 数据库索引案例
MySQL 索引可以用来快速地寻找某些具有特定值的记录,所有 MySQL 索引都以 B-树的形式保存。例如 MYSQL 没有索引,执行 select 时 MySQL 必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。 如果表中数据有上亿条数据,查询一条数据花费的时间会非常长,索引的目的就类似电子书的目录及页码的对应关系。
如果在需搜索条件的列上创建了索引,MySQL 无需扫描全表记录即可快速得到相应的记录行。如果该表有 1000000 条记录,通过索引查找记录至少要比全表顺序扫描快至少100 倍,这就是索引在企业环境中带来的执行速度的提升。
MYSQL 数据库常见索引类型包括:普通索引(normal)、唯一索引(unique)、全文索引(full text)、主键索引(primary key)、组合索引等,如下为每个索引的应用场景
及区别:
普通索引: normal,使用最广泛;
唯一索引: unique,不允许重复的索引,允许有空值;
全文索引: full text,只能用于 MyISAM 表,FULLTEXT 主要用于大量的内容检索;
主键索引: primary key 又称为特殊的唯一索引,不允许有空值;
组合索引: 为提高 mysql 效率可建立组合索引;MYSQL 数据库表创建各个索引命令,以 t1 表为案例,操作如下:
主键索引 ALTER TABLE t1 ADD PRIMARY KEY ( `column` );
唯一索引 ALTER TABLE t1 ADD UNIQUE (`column`);
普通索引 ALTER TABLE t1 ADD INDEX index_name ( `column` );
全文索引 ALTER TABLE t1 ADD FULLTEXT ( `column` );
组合索引 ALTER TABLE t1 ADD INDEX index_name ( `column1`, `column2`,`column3` );
mysql> ALTER TABLE t1 ADD PRIMARY KEY ( `id` );
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 ADD UNIQUE (`id`);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE t1 ADD INDEX index_name ( `id` );
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from t1;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | | |
| t1 | 0 | id | 1 | id | A | 1 | NULL | NULL | | BTREE | | |
| t1 | 1 | index_name | 1 | id | A | 1 | NULL | NULL | | BTREE | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE t1 ADD INDEX index_id ( `id` );
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> ALTER TABLE t1 DROP INDEX index_name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | | |
| t1 | 0 | id | 1 | id | A | 1 | NULL | NULL | | BTREE | | |
| t1 | 1 | index_id | 1 | id | A | 1 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
MYSQL 数据库表删除各个索引命令,以 t1 表为案例,操作如下:
DROP INDEX index_name ON t1;
ALTER TABLE t1 DROP INDEX index_name;
ALTER TABLE t1 DROP PRIMARY KEY;
MYSQL 数据库查看表索引:
show index from t1;
show keys from t1;
MYSQL 数据库索引的缺点:
❑ MYSQL 数据库索引虽然能够提高数据库查询速度,但同时会降低更新、删除、插入表的速度,例如如对表进行 INSERT、UPDATE、DELETE 时,update 表 MySQL不仅要保存数据,还需保存更新索引;
❑ 建立索引会占用磁盘空间,大表上创建了多种组合索引,索引文件的会占用大量的空间。
MySQL 数据库慢查询
MYSQL 数据库慢查询主要用于跟踪异常的 SQL 语句,可以分析出当前程序里那些 Sql语句比较耗费资源,慢查询日志则用来记录在 MySQL 中响应时间超过阀值的语句,具体指运行时间超过 long_query_time 值的 SQL 语句,会被记录到慢查询日志中。
Mysql 数据库默认没有开启慢查询日志功能,需手动在配置文件或者MYSQL 命令行中开启,慢查询日志默认写入磁盘中的文件,也可以将慢查询日志写入到数据库表。
查看数据库是否开启慢查询,如图 所示,命令如下:
show variables like "%slow%";
show variables like "%long_query%";
mysql> show variables like "%slow%";
+---------------------------+-----------------------------------------------------------+
| Variable_name | Value |
+---------------------------+-----------------------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /data/mysql/hecs-x-medium-2-linux-20200611091300-slow.log |
+---------------------------+-----------------------------------------------------------+
5 rows in set (0.00 sec)
mysql> show variables like "%long_query%";
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
MYSQL 慢查询参数详解如下:
log_slow_queries 关闭慢查询日志功能;
long_query_time 慢查询超时时间,默认为 10s,MYSQL5.5 以上可以设置微秒;
slow_query_log 关闭慢查询日志;
slow_query_log_file 慢查询日志文件;
slow_launch_time Thread create 时间,单位秒,如果 thread create 的时间
超过了这个值,该变量 slow_launch_time 的值会加 1;
log-queries-not-using-indexes 记录未添加索引的 SQL 语句。
开启 MYSQL 慢查询日志方法有两种:
(1) Mysql 数据库命令行执行命令:
set global slow_query_log=on;
show variables like "%slow%";
(2) 编辑 my.cnf 配置文件中添加如下代码:
log-slow-queries = /data/mysql/localhost.log
long_query_time = 0.01
log-queries-not-using-indexes
慢查询功能开启之后,数据库会自动将执行时间超过设定时间的 SQL 语句添加至慢查询日志文件中,可以通过慢查询日志文件定位执行慢的 SQL,从而对其优化,可以通过mysqldumpslow 命令行工具分析日志,相关参数如下:
执行命令 mysqldumpslow -h 可以查看命令帮助信息:
主要参数包括:-s 和-t
-s 这个是排序参数,可选的有:
l: 查询锁的总时间;
r: 返回记录数;
t: 查询总时间排序;
al: 平均锁定时间;
ar: 平均返回记录数;
at: 平均查询时间;
c: 计数;
-t n 显示头 n 条记录。
MYSQL 慢查询 mysqldumpslow 按照返回的行数从大到小,查看前 2 行,如图 命令如下:
mysqldumpslow -s r -t 2 localhost.log #服务器执行,最好进入到/usr/local/mysql-5.7.29/bin/ 目录下执行
MYSQL慢查询mysqldumpslow按照查询总时间从大到小,查看前5行,同时过滤select的 sql 语句
mysqldumpslow -s t -t 5 -g "select" localhost.log
MySQL 数据库优化
Mysql 数据库优化是一项非常重要的工作,而且是一项长期的工作,MYSQL 优化三分靠配置文件及硬件资源的优化,七分靠 sql 语句的优化。
Mysql 数据库具体优化包括:配置文件的优化、sql 语句的优化、表结构的优化、索引的优化,而配置的优化包括:系统内核优化、硬件资源、内存、CPU、mysql 本身配置文件的优化。
硬件上的优化:增加内存和提高磁盘读写速度,都可以提高 MySQL 数据库的查询,更新的速度。另一种提高 MySQL 性能的方式是使用多块磁盘来存储数据。因为可以从多块磁盘上并行读取数据,这样可以提高读取数据的速度。
MySQL 参数的优化:内存中会为 MySQL 保留部分的缓冲区,这些缓冲区可以提高 MySQL的速度,缓冲区的大小可以在 MySQL 的配置文件中进行设置。
附企业级 MYSQL 百万量级真实环境配置文件 my.cnf 内容,可以根据实际情况修改:
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
user = mysql
server_id = 10
port = 3306
socket = /tmp/mysql.sock
datadir = /data/mysql/
old_passwords = 1
lower_case_table_names = 1
character-set-server = utf8
default-storage-engine = MYISAM
log-bin = bin.log
log-error = error.log
pid-file = mysql.pid
long_query_time = 2
slow_query_log
slow_query_log_file = slow.log
binlog_cache_size = 4M
binlog_format = mixed
max_binlog_cache_size = 16M
max_binlog_size = 1G
expire_logs_days = 30
ft_min_word_len = 4
back_log = 512
max_allowed_packet = 64M
max_connections = 4096
max_connect_errors = 100
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
sort_buffer_size = 2M
query_cache_size = 64M
table_open_cache = 10000
thread_cache_size = 256
max_heap_table_size = 64M
tmp_table_size = 64M
thread_stack = 192K
thread_concurrency = 24
local-infile = 0
skip-show-database
skip-name-resolve
skip-external-locking
connect_timeout = 600
interactive_timeout = 600
wait_timeout = 600
#*** MyISAM
key_buffer_size = 512M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 1G
myisam_repair_threads = 1
concurrent_insert = 2
myisam_recover
#*** INNODB
innodb_buffer_pool_size = 64G
innodb_additional_mem_pool_size = 32M
innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 120
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_thread_concurrency = 16
innodb_open_files = 10000
#innodb_force_recovery = 4
#*** Replication Slave
read-only
#skip-slave-start
relay-log = relay.log
log-slave-updates
MySQL 数据库集群实战
随着访问量的不断增加,单台 MySQL 数据库服务器压力不断增加,需要对 MYSQL 进行优化和架构改造,MYQSL 优化如果不能明显改善压力情况,可以使用高可用、主从复制、读写分离来、拆分库、拆分表来进行优化。
MYSQL 主从复制集群在中小企业、大型企业中被广泛使用,MYSQL 主从复制的目的是实现数据库冗余备份,将 Master 数据库数据定时同步至 Slave 库中,一旦 Master 数据库宕机,可以将 WEB 应用数据库配置快速切换至 Slave 数据库,确保 WEB 应用较高的可用率,如图 11-12 所示,为 MYSQL 主从复制结构图:
Mysql 主从复制集群至少需要 2 台数据库服务器,其中一台为 Master 库,另外一台为 Slave 库,MYSQL 主从数据同步是一个异步复制的过程,要实现复制首先需要在 master上开启 bin-log 日志功能,bin-log 日志用于记录在 Master 库中执行的增、删、修改、更新操作的 sql 语句,整个过程需要开启 3 个线程,分别是 Master 开启 IO 线程,Slave开启 IO 线程和 SQL 线程,具体主从同步原理详解如下:
❑ Slave 上执行 slave start,Slave IO 线程会通过在 Master 创建的授权用户连接上至Master,并请求master从指定的文件和位置之后发送bin-log日志内容;
❑ Master 接收到来自 slave IO 线程的请求后,master IO 线程根据 slave 发送的指定 bin-log 日志 position 点之后的内容,然后返回给 slave 的 IO 线程。
❑ 返回的信息中除了 bin-log 日志内容外,还有 master 最新的 binlog 文件名以及在 binlog 中的下一个指定更新 position 点;
❑ Slave IO 线程接收到信息后,将接收到的日志内容依次添加到 Slave 端的relay-log 文件的最末端,并将读取到的 Master 端的 bin-log 的文件名和
position 点记录到 master.info 文件中,以便在下一次读取的时候能告知master 从响应的 bin-log 文件名及最后一个 position 点开始发起请求;
❑ Slave Sql 线程检测到 relay-log 中内容有更新,会立刻解析 relay-log 的内容成在 Master 真实执行时候的那些可执行的 SQL 语句,将解析的 SQL 语句并在Slave 里执行,执行成功后,Master 库与 Slave 库保持数据一致。
MySQL 主从复制实战
MYSQL主从复制环境构建至少需2台服务器,可以配置1主多从,多主多从,如下为1主1从,MYSQL 主从复制架构实战步骤:
(1) 系统环境准备
Master:192.168.111.128
Slave: 192.168.111.129
(2) Master 安装及配置
Master 端使用源码安装 MySQL-5-6 版本软件后,在/etc/my.cnf 配置文件[mysqld]
段中加入如下代码,然后重启 MYSQL 服务即可。
server-id = 1
log-bin = mysql-bin
Master 端/etc/my.cnf 完整配置代码如下:
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
Master 数据库服务器命令行中 创建 tongbu 用户及密码并设置权限,执行如下命令,查看 bin-log 文件及 position 点,如图所示:
grant replication slave on *.* to 'tongbu'@'%' identified by
'123456';
show master status;
(3)Slave 安装及配置
Slave端使用源码安装 MySQL-5-6版本软件后,在/etc/my.cnf 配置文件[mysqld]段中加入如下代码,然后重启 MYSQL 服务即可。如果在安装时 cp my-large.cnf /etc/my.cnf,则需修改server-id,MASTER与Slave端 server-id不能一样,Slave 端也无需开启 bin-log 功能:
server-id = 2
Slave 端/etc/my.cnf 完整配置代码如下:
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8
server-id = 2
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
Slave指定Master IP、用户名、密码、bin-log文件名(mysql-bin.000028)及position
(257),代码如下:
change master to master_host='192.168.1.115',master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=297;
在 slave 启动 slave start,并执行 show slave status\G 查看 Mysql 主从状态:
slave start;
show slave status\G
查看 Slave 端 IO 线程、SQL 线程状态均为 YES,代表 Slave 已正常连接 Master 实现
同步:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
执行 Show slave status\G,常见参数含义解析:
Slave_IO_State I/O 线程连接 Master 状态;
Master_User 用于连接 Master 的用户;
Master_Port Master 端监听端口;
Connect_Retry 主从连接失败,重试时间间隔;
Master_Log_File I/O 线程读取的 Master 二进制日志文件的名称。
Read_Master_Log_Pos I/O 线程已读取的 Master 二进制日志文件的位置;
Relay_Log_File SQL 线程读取和执行的中继日志文件的名称。
Relay_Log_Pos SQL 线程已读取和执行的中继日志文件的位置;
Relay_Master_Log_File SQL 线程执行的 Master 二进制日志文件的名称;
Slave_IO_Running I/O 线程是否被启动并成功地连接到主服务器上;
Slave_SQL_Running SQL 线程是否被启动;
Replicate_Do_DB 指定的同步的数据库列表;
Skip_Counter SQL_SLAVE_SKIP_COUNTER 设置的值;
Seconds_Behind_Master Slave端SQL线程和I/O线程之间的时间差距,单位为秒,常被用于主从延迟检查方法之一。
在 Master 端创建 mysql_db_test 数据库和 t0 表,如图 11-14 所示,命令如下:
create database mysql_ab_test charset=utf8;
show databases;
use mysql_ab_test;
create table t0 (id varchar(20),name varchar(20));
show tables;
Slave 服务器查看是否有 mysql_ab_test 数据库和 t0 的表,如果存在则代表 Slave
从 Master 复制数据成功,证明 MYSQL 主从配置至此已经配置成功,如图 11-15 所示:
在 Master 服务器的 t0 表插入两条数据,在 slave 查看是否已同步,master 上执行如
下图 11-16 所示:
insert into t0 values (“001”,”wugk1”);
insert into t0 values (“002”,”wugk2”);
select * from t0;
Slave 端执行查询命令,如图 11-17 所示,表示在 Master 插入的数据已经同步到 Slave
端:
MySQL 主从同步排错思路
Mysql 主从同步集群在生成环境使用过程中,如果主从服务器之间网络通信条件差或者数据库数据量非常大,容易导致 MYSQL 主从同步延迟。
MYSQL 主从产生延迟之后,一旦主库宕机,会导致部分数据没有及时同步至丛库,重新启动主库,会导致丛库与主库同步错误,如何快速恢复主从同步关系呢,如下有两种方法:
(1) 忽略错误后,继续同步:
此种方法适用于主从库数据内容相差不大,或者要求数据可以不完全统一的情况,数据要求不严格的情况。
Master 端执行如下命令,将数据库设置全局读锁,不允许写入新数据:
flush tables with read lock;
Slave 端停止 Slave I/O 及 sql 线程,同时将同步错误的 SQL 跳过 1 次,跳过会导致
数据不一致,最后启动 start slave,同步状态恢复,命令如下:
stop slave;
set global sql_slave_skip_counter =1;
start slave;
(2) 重新做主从同步,完全同步:
此种方法适用于主从库数据内容相差很大,或者要求数据完全统一的情况,数据需完全保持一致。
Master 端执行如下命令,将数据库设置全局读锁,不允许写入新数据:
flush tables with read lock;
Master 端基于 mysqldump、xtrabackup 工具进行数据库将完整的数据库备份,也可以
用 shell 脚本或 python 脚本实现定时备份,备份成功之后,将完整的数据导入至丛库,
重新配置主从关系,当 Slave 端的 IO 线程、SQL 线程均为 YES 之后,最后将 Master 端读
锁解开即可,解锁命令如下:
unlock tables;