/*查看改用户的所有权限*/
show grants for 'root'@'localhost';
/*设置匹配所有权限*/
 grant all privileges on *.* to 'root'@'%';
/*刷新权限*/
 flush privileges;
1
2
3
4
5
6

安装mysql

bin/mysqld --defaults-file=/usr/local/mysql2/my.cnf --initialize --user=mysql
#得到密码后登录服务器 设置全域访问并修改密码,此时安装完成
1
2

mysql 配置文件的加载顺序

mysql数据库总结

mysql 数据库是系统数据库，包含存储 MySQL 服务器运行时所需信息的表。
表作用分为一下几类:

• 授予系统表 Grant System Tables
• 对象信息系统表 Object Information System Tables
• 记录系统表 Log System Tables
• 服务器端帮助系统表 Server-Side Help System Tables
• 时区系统表 Time Zone System Tables
• 复制系统表 Replication System Tables
• 优化器系统表 Optimizer System Tables
• 杂项系统表 Miscellaneous System Tables

重点说一下 复制系统的表，以方便我们日后学习 数据库的 读写分离。
gtid_executed：用于存储 GTID 值的表,使用 InnoDB存储引擎。
ndb_binlog_index: NDB Cluster 复制的二进制日志信息。
slave_master_info, slave_relay_log_info, slave_worker_info: 用于在副本服务器上存储复制信息。

mysql 服务器日志

开启通用日志功能

log_output=FILE
general_log=ON
general_log_file=general.log
1
2
3

重点讨论一下binary log 日志

binary log 日志的格式

• 基于 SQL 语句 binlog-format=STATEMENT
• 基于行的日志 binlog-format=ROW
• 混合日志记录 binlog-format=MIXED

更改 bin log 格式的注意事项

• 开启主从同步的情况下，如果 主从不同步修改，就会报错
• 使用 读已提交 和 读未提交的事务隔离级别，只能使用 基于行的日志格式

针对不同的存储引擎设置不同的日志格式

bin log 在以下时机会生成新的bin log 日志,小标从1开始自增

• 服务器启动或重新启动
• 服务器刷新日志
• 当前日志文件大小达到 max_binlog_size

bin log 日志的记录动作是在未提交的事务中，所有更改都将被缓存，直到 服务器接收到提交语句。mysqld在执行之前将整个事务写入二进制日志 。 当处理事务的线程启动时，它会binlog_cache_size为缓冲区语句分配一个缓冲区。如果语句大于此值，则线程会打开一个临时文件来存储事务。线程结束时删除临时文件。

binlog 配置

server-id=1
log-bin=/usr/local/mysql/mysql-files/binLog
log_bin_index=/usr/local/mysql/mysql-files/binLogIndex
binlog_cache_size=4096
max_binlog_cache_size=4096
sync_binlog=1
binlog_format=MIXED
1
2
3
4
5
6
7

/* 设置全局变量开启bin log*/
SET GLOBAL log_bin = ON;
1
2

慢查询日志

#慢查询日志
slow_query_log=ON
long_query_time=10
min_examined_row_limit=10000
slow_query_log_file=slow.log
log_output=FILE
1
2
3
4
5
6

备份

#备份所有数据库
./bin/mysqldump --all-databases -uroot -p > dump.sql
#备份指定的数据库
 mysqldump --databases db1 db2 db3 > dump.sql
 #刷新并备份数据
 mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > backup_sunday_1_PM.sql
 #备份并删除 二进制文件
mysqldump --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > backup_sunday_1_PM.sql

 # 导入sql
 mysql < dump.sql
#sql 中不包含创建表的语句的话
mysqladmin create db1
mysql db1 < dump.sql
#或
CREATE DATABASE IF NOT EXISTS db1;
USE db1;
source dump.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

/*查看所有日志*/
SHOW BINARY LOGS;
/*查看当前日志  */
SHOW MASTER STATUS;

flush logs;
1
2
3
4
5
6

全量恢复

/*增量备份恢复 方式一*/
mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
/*增量备份恢复 方式二*/
mysqlbinlog binlog.000001 >  /tmp/statements.sql
mysqlbinlog binlog.000002 >> /tmp/statements.sql
mysql -u root -p -e "source /tmp/statements.sql"
1
2
3
4
5
6

使用事件位置的时间点恢复

mysqlbinlog   --start-datetime="2020-05-27 12:59:00" --stop-datetime="2020-05-27 13:06:00" \
  --verbose /var/lib/mysql/bin.123456 | grep -C 12 "DROP TABLE"

# 恢复到指定的事件点
mysqlbinlog --start-position=4 --stop-position=1548 binLog.000006 | mysql -u root -p

#从指定的事件点开始
mysqlbinlog --start-position=3137 binLog.000006 | mysql -u root -p
1
2
3
4
5
6
7
8

提示 在执行备份的之前尽量手动执行一下 flush logs; 生成新的备份文件，避免binLog 的重复记录

聚集索引与二级索引

每个InnoDB表都有一个特殊的索引，称为聚集索引，用于存储行数据。通常，聚集索引与主键同义。

1. 有PRIMARY KEY，InnoDB将其用作聚集索引。
2. 如果您没有PRIMARY KEY，InnoDB则使用第一个 UNIQUE索引，其中所有键列都定义为NOT NULL。
3. 如果表没有索引PRIMARY KEY或没有合适 的UNIQUE索引，则InnoDB 生成一个隐藏的聚集索引 ，该索引以GEN_CLUST_INDEX包含行 ID 值的合成列命名。

聚集索引以外的索引称为二级索引。在InnoDB中，二级索引中的每条记录都包含该行的主键列，以及为二级索引指定的列。 InnoDB使用此主键值在聚集索引中搜索行。

复制

基于二进制日志文件位置的复制

1. 源服务器 创建复制权限的账号及权限

CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
1
2

2. 备份源服务器的日志文件

./bin/mysqldump --all-databases -uroot -p > dump.sql
1

3. 安装副本服务器
4. 配置副本服务器配置

[mysqld]
port=3307
datadir=/usr/local/mysql2/mysql-files
socket=/tmp/mysql2.sock
risks
symbolic-links=0
#server id
server-id=2
[mysqld_safe]
log-error=/usr/local/mysql2/mysql-files/mariadb.log
pid-file=/usr/local/mysql2/mysql-files/mariadb.pid
!includedir /etc/my.cnf.d
1
2
3
4
5
6
7
8
9
10
11
12

5. 使用指定配置文件启动从服务器
6. 在从服务器上配置源服务器信息,post 和文件 可以从源服务器查看。

   /*可以从源服务器查看*/
   SHOW MASTER STATUS;
   1
   2

   /*在从服务器上配置源服务器信息,post 和文件 */
   CHANGE MASTER TO
        MASTER_HOST='localhost',
    			MASTER_PORT=3306,
        MASTER_USER='repl',
        MASTER_PASSWORD='123456',
        MASTER_LOG_FILE='binLog.000012',
        MASTER_LOG_POS=154;
   1
   2
   3
   4
   5
   6
   7
   8

7. 为源服务器导入全量数据
8. 开启源服务器SLAVE 线程

   START SLAVE;
   1

完成主从配置。