mysql基础

编译安装

mariadb

1、卸载mysql

rpm -qa | grep mysql
rpm -e --nodeps mysql-libs
rpm -e --nodeps mysql
rpm -e --nodeps mysql-server
find / -name mysql #查找mysql开头的库或文件，依次删除
rm -fr /var/lib/mysql
rm -fr /usr/lib/mysql
rm -fr /usr/include/mysql
rm -f /etc/my.cnf

2、准备工作

groupadd mysql
useradd -g mysql mysql -s /sbin/nologin -M
mkdir -p /usr/local/mysql
mkdir -p /usr/local/mysql/data
mkdir -p /usr/local/mysql/data/temp
mkdir -p /var/log/mysql
chown -R mysql:mysql /usr/local/mysql/
chown -R mysql:mysql /usr/local/mysql/data/
chown -R mysql:mysql /var/log/mysql
chown -R mysql:mysql /usr/local/mysql/data/temp

yum -y install make gcc-c++ cmake bison-devel ncurses-devel readline-devel zlib-devel openssl-devel libaio-devel #MySQL5.5及以上版本都开始用cmake方式进行配置编译，所以先安装cmake及依赖库,注意提前配置yum源为aliyun，官方yum源中没有cmake。

cd /usr/local/src/
wget https://downloads.mariadb.org/interstitial/mariadb-10.0.37/source/mariadb-10.0.37.tar.gz/from/http%3A//mirrors.tuna.tsinghua.edu.cn/mariadb/ #下载安装包

3、配置编译安装

#解压后进入到解压目录
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DSYSCONFDIR=/etc \
-DWITHOUT_TOKUDB=1 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DMYSQL_USER=mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci

#注意：-DCMAKE_INSTALL_PREFIX是指定安装的位置，这里是/usr/local/mysql，-DMYSQL_DATADIR是指定MySQL的数据目录，这里是/usr/local/mysql/data，安装目录和数据目录都可以自定义设置，-DSYSCONFDIR是指定配置文件所在的目录，一般都是/etc ，具体的配置文件是/etc/my.cnf，-DWITHOUT_TOKUDB=1这个参数一般都要设置上，表示不安装tokudb引擎，tokudb是MySQL中一款开源的存储引擎，可以管理大量数据并且有一些新的特性，这些是Innodb所不具备的，这里之所以不安装，是因为一般计算机默认是没有Percona Server的，并且加载tokudb还要依赖jemalloc内存优化，一般开发中也是不用tokudb的，所以暂时屏蔽掉，否则在系统中找不到依赖会出现：CMake Error at storage/tokudb/PerconaFT/cmake_modules/TokuSetupCompiler.cmake:179 (message)这样的错误。-DMYSQL_UNIX_ADDR参数指定了mysql的通讯sock文件地址，此地址和php.ini中的mysqli.default_socket、mysql.default_socket、pdo_mysql.default_socket参数路径保持一致，php.ini中的默认路径为/tmp/mysql.sock。如果万一执行中有了错误，可以执行： rm -f CMakeCache.txt 删除编译缓存，让指令重新执行，否则每次读取这个文件，命令修改正确也是报错
    
make && make install 
chown -R mysql:mysql /usr/local/mysql/
chmod -R 1777 /tmp #调整tmp权限，否则初始化会报错。

cd /usr/local/mysql
./scripts/mysql_install_db --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql --user=mysql #运行安装脚本
cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf 
#实测不拷贝也可正常启动，运行。一种解释是mysql启动时会自动去/etc/ 或安装目录下查找my.cnf文件，如果没有，就是用系统内置参数（可能是配置编译时指定的）。
#/usr/local/mysql/bin/mysqld --verbose --help |grep -A 1 'Default options' 或者 mysql --help|grep 'my.cnf' 
#这两条命令可以查询到mysql系统使用的的配置文件路径，mysql启动时按顺序查找。

cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld #拷贝启动文件到系统启动目录下
chkconfig --add mysqld   # 添加至系统服务，可以使用systemctl或service
echo "export PATH=$PATH:/usr/local/mysql/bin/" >/etc/profile.d/mysql.sh #给mysql相关命令，配置环境变量配置文件，放到/etc/profile.d目录下。
source /etc/profile

4、测试验证

登陆故障
[root@zqq-study ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@zqq-study ~]# pkill mysqld
rm -rf /usr/local/mysql/data/*    #删除data文件夹下所有文件

#然后重新初始化
cd /usr/local/mysql
./scripts/mysql_install_db --datadir=/usr/local/mysql/data --basedir=/usr/local/mysql --user=mysql #运行安装脚本

优化：
#开启慢查询
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1
drop database test; #删除测试库
delete from mysql.user where user=' ' #删除系统默认空账户
delete from mysql.user where user='www' #删除系统默认www账号

重启：
systemctl restart mariadb
mysql> show variables like "%character%";show variables like "%collation%"; //验证字符集配置

mysql5.7.2安装

由于mysql 5.7.17版本以后 support_files文件夹中无 my_default.cnf 文件，所以今天给大家详细描述一下 mysql 5.7.20版本(目前官方最新版)的安装步骤。

第一步：下载mysql最新版

wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

第二步：在/usr/local/中解压压缩包，并改名为mysql

cd /usr/local/
tar -xzvf /my_data/software/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz# (我的下载目录为 my_data/software)
ln -s mysql-5.7.20-linux-glibc2.12-x86_64 mysql

第三步：创建用户组及相关目录

groupadd mysql
useradd -g mysql mysql -s /sbin/nologin -M
mkdir -p /usr/local/mysql/data
mkdir -p /var/log/mysql
chown -R mysql:mysql /usr/local/mysql/
chown -R mysql:mysql /usr/local/mysql/data/
chown -R mysql:mysql /var/log/mysql

第四步：创建配置文件

vim /etc/my.cnf

#复制以下内容，保存关闭

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
character_set_server=utf8
init_connect='SET NAMES utf8'
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#不区分大小写
lower_case_table_names = 1

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

max_connections=5000

default-time_zone = '+8:00'

第五步：初始化数据库

#先安装一下这个东东，要不然初始化有可能会报错
yum install libaio
#手动编辑一下日志文件，什么也不用写，直接保存退出
cd /var/log/mysql

vim mysqld.log
：wq

chmod 777 mysqld.log
chown mysql:mysql mysqld.log

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --lc_messages_dir=/usr/local/mysql/share --lc_messages=en_US

第六步：查看初始密码

cat /var/log/mysql/mysqld.log #最后一行root@localhost: dNwfii#1eE0/ 这里就是初始密码

第七步：启动服务

#然后执行如下操作开启mysql服务，以及设置相关权限
cd /var/run/
mkdir mysqld
chmod 777 mysqld
cd mysqld
vim mysqld.pid
chmod 777 mysqld.pid
cd ..
chown -R mysql:mysql  mysqld

/usr/local/mysql/support-files/mysql.server start #启动mysql服务

第八步：进入msql重置密码及相关配置

/usr/local/mysql/bin/mysql -uroot -p你在上面看到的初始密码
#如果提示必须要修改密码才可以进行操作的话则执行下面操作
set password=password('新密码');
flush privileges;

UPDATE `mysql`.`user` SET `Host` = '%',  `User` = 'root'  WHERE (`Host` = 'localhost') AND (`User` = 'root');
flush privileges;

第九步：加入开机自启动

cd /usr/local/mysql/support-files
cp mysql.server /etc/init.d/mysqld
chkconfig --add mysqld
#centos7中完成此步骤即可使用systemctl 管理mysqld.service

第十步：管理配置优化

#创建远程登录用户
grant all privileges on *.* to '新用户名'@'%' identified by '新密码'; 
flush privileges;

#添加系统路径
vim /etc/profile
export PATH=/usr/local/mysql/bin:$PATH
source /etc/profile

基本操作

一、登陆mariadb

1、连接到本机上的mariadb mysql -uroot -p \\安装默认无密码
2、连接到远程主机上的mariadb
mysql -h10.195.236.135 -uroot -pabc12345
3、mysql远程连接授权管理
mysql -uroot -p
use mysql; //切换到mysql数据库中
select host,user,password from user; //查看当前系统user表中允许登陆用户信息
update user set host = '10.195.249.225' where user= 'root'; //修改mysql库的user表，将指定用户的host项修改%或指定IP.此操作可能报错可不管。
select host,user,password from user; //查看当前系统user表中允许登陆用户信息
flush privileges;
4、exit //退出数据库登陆

二、常用查询

1、show databases; show tables;

2、desc table_name; #查询表结构

3、show grants; #查看当前登录用户权限

4、show grants for user_name; #查看user_name用户的权限

三、系统管理

mysqladmin -uroot password oldboy123 #给mysql管理员root配置密码，安装后密码为空
登陆mariadb
格式: mysql -h 主机地址 -u用户名 -p用户密码
# mysql -uroot -pfhwfhw123!

# mysql -uroot -pfhwfhw123! -e "show variables like '%_log_%';" #直接使用linux命令行执行mysql命令，查询log日志开启情况及路径。

修改密码
格式: update mysql.user set password=password('新密码') where user ='用户名'
>use mysql;
>update user set password=password('fhwpbx') where user='dsh';
>flush privileges;

新增用户(并授权登陆及查询):
格式:grant select on 数据库.* to 用户名@登录主机 identified by '密码'
>grant select,insert on sbc.* to dsh@localhost identified by ‘dsh’;
>flush privileges;

查看用户权限&回收用户权限
>show grants for dsh@localhost;
>flush privileges;
>revoke select,insert on mysql.* from dsh@localhost;
>flush privileges;

配置mariadb用户远程登陆权限：
GRANT ALL PRIVILEGES ON *.* to 'root'@'10.199.49.60' identified by 'fhwfhw123!';

查看删除用户：
>use mysql;
>select host,user from user;
>delete from user where user='dsh' and host='localhost';
>flush privileges;

创建数据库
>create database zzpbx charset utf8;

导入sql文件

mysql -uroot -poldboy123 </tmp/bak.sql #导入数据库文件
mysql -uroot -poldboy123 -e "show databases like 'wordpress';" #在linux端使用mysql命令直接执行sql语句。

导入数据文件进mysql数据表中：
>load data infile "/home/sbc/log/2018041304.inv" into table invite fields terminated by ',' enclosed by '\'';

最后：忘记root密码修改

#mysql5.7修改root密码
vim /etc/my.conf
skip-grant-tables #添加到[mysqld]下，wq退出
/etc/init.d/mysqld restart
mysql -uroot -p #无密码登陆
update mysql.user set authentication_string = password('fhwfhw123!') where user='root'; #注意5.7user表密码字段使用authentication_string

#skip-grant-tables #去除到[mysqld]下的这句话，wq退出
/etc/init.d/mysqld restart

备份导出导入：

#该命令会导出包括系统数据库在内的所有数据库
mysqldump -uroot -proot --all-databases >/tmp/all.sql

#导出db1、db2两个数据库的所有数据
mysqldump -uroot -proot --databases db1 db2 >/tmp/db1_and_db2.sql

#导出db1中的a1、a2表(注意导出指定表只能针对一个数据库进行导出，且导出的内容中和导出数据库也不一样，导出指定表的导出文本中没有创建数据库的判断语句，只有删除表-创建表-导入数据.)
mysqldump -uroot -proot --databases db1 --tables a1 a2 >/tmp/db1.sql

#条件导出，导出db1表a1中id=1的数据
mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' >/tmp/a1.sql

#只导出表结构不导出数据，--no-data
mysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql

#将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错
mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2

#导出数据库：（不带create databasename ，不带use databasename，以便在导入时指定新的数据库名，新的数据库要存在;还是要修改use databasesName）
mysqldump -uroot -proot  -hxxx.xxx.xxx.xxx --no-create-db  databaseName >databaseName.sql

1. 只导出表结构不导表数据，添加“-d”命令参数

mysqldump -uroot -h127.0.0.1 -proot -P3306 -d education users>d:/user.sql

2.只导出表数据不导表结构，添加“-t”命令参数

mysqldump -uroot -h127.0.0.1 -proot -P3306 -t education users>d:/user.sql

3.导出数据库的表结构和表数据（导入的时候需要指定数据库，保证指定的数据库存在）

导出命令：mysqldump -h127.0.0.1 -P3306 -uroot -proot education >d:/database.sql

导入命令：mysql -uroot -proot -h127.0.0.1 -P3306 education<d:/database.sql

配置文件优化

vim /etc/my.cnf
[client]
#########################################################################
#                                                                       #
#                         MySQL客户端配置                               #
#                                                                       #
#########################################################################
port = 3306 # MySQL客户端默认端口号
socket = /tmp/mysql.sock# 用于本地连接的Unix套接字文件存放路径
default-character-set = utf8mb4 # MySQL客户端默认字符集
[mysql]
#########################################################################
#                                                                       #
#                         MySQL命令行配置                               #
#                                                                       #
#########################################################################
auto-rehash # 开启tab补齐功能
socket = /tmp/mysql.sock # 用于本地连接的Unix套接字文件存放路径
default-character-set = utf8mb4 # MySQL客户端默认字符集
max_allowed_packet = 256M # 指定在网络传输中一次消息传输量的最大值。系统默认值为1MB，最大值是1GB，必须设置1024的倍数。

[mysqld]
#########################################################################
#                                                                       #
#                         MySQL服务端配置General                        #
#                                                                       #
#########################################################################
port = 3306 #指定了Mysql开放的端口；
basedir = /usr/local/mysql #指定Mysql安装的绝对路径；
datadir = /usr/local/mysql/data #指定Mysql数据存放的绝对路径；
socket = /tmp/mysql.sock #若报错提示此文件错误，首先find一下该文件的路径，然后配置指向正确的路径即可；其次误删除了，此时需重启mysql服务，重启完后会在datadir目录下生成一个该文件，然后配置指向正确的路径就可以了；
plugin_dir = /usr/local/mysql/lib/plugin #mysql中plugin插件所在的路径；    

pid_file = /usr/local/mysql/data/mysql.pid # 进程ID文件存放路径
tmpdir = /usr/local/mysql/data/temp # MySQL临时文件存放路径
character_set_server = utf8mb4 # MySQL服务端字符集
collation_server = utf8mb4_general_ci # MySQL服务端校对规则
default-storage-engine = InnoDB # 设置默认存储引擎为InnoDB
autocommit = OFF # 默认为ON，设置为OFF，关闭事务自动提交

transaction_isolation = READ-COMMITTED
# MySQL支持4种事务隔离级别，他们分别是：
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE.
# 如没有指定，MySQL默认采用的是REPEATABLE-READ，ORACLE默认的是READ-COMMITTED

event_scheduler = ON # 开启事件调度器event_scheduler
#explicit_defaults_for_timestamp = ON # 控制TIMESTAMP数据类型的特性，默认OFF，设置为ON，update 时timestamp列关闭自动更新。（将来会被废弃）
lower_case_table_names = 1 # 库名、表名是否区分大小写。默认为0，设置1，不区分大小写，创建的表、数据库都以小写形式存放磁盘。

########################################
#                                      #
#       Network & Connection           #
#                                      #
########################################
max_connections = 1000 # MySQL允许的最大并发连接数，默认值151，如果经常出现Too Many Connections的错误提示，则需要增大此值。
max_user_connections = 1000 # 每个数据库用户的最大连接，（同一个账号能够同时连接到mysql服务的最大连接数），默认为0，表示不限制。
back_log = 500 # MySQL监听TCP端口时设置的积压请求栈大小，默认50+(max_connections/5)，最大不超过900
max_connect_errors = 10000 # 每个主机的连接请求异常中断的最大次数。对于同一主机，如果有超出该参数值个数的中断错误连接，则该主机将被禁止连接。如需对该主机进行解禁，执行：FLUSH HOST。
interactive_timeout = 28800 # 服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。默认值：28800秒（8小时）
wait_timeout = 28800
# 服务器关闭非交互连接之前等待活动的秒数。默认值：28800秒（8小时）
# 指定一个请求的最大连接时间，当MySQL连接闲置超过一定时间后将会被强行关闭。对于4GB左右内存的服务器来说，可以将其设置为5~10。
# 如果经常出现Too Many Connections的错误提示，或者show processlist命令发现有大量sleep进程，则需要同时减小interactive_timeout和wait_timeout值。
connect_timeout = 28800 # 在获取连接时，等待握手的超时秒数，只在登录时生效。主要是为了防止网络不佳时应用重连导致连接数涨太快，一般默认即可。
open_files_limit = 5000 # mysqld能打开文件的最大个数，默认最小1024，如果出现too mant open files之类的就需要增大该值。
max_allowed_packet = 256M # 指定在网络传输中一次消息传输量的最大值。系统默认值 为1MB，最大值是1GB，必须设置1024的倍数。

########################################
#                                      #
#          Thread & Buffer             #
#                                      #
########################################
sort_buffer_size = 2M # 排序缓冲区大小，connection级参数，默认大小为2MB。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引，其次可以尝试增大该值。
read_buffer_size = 160M # 顺序读缓冲区大小，connection级参数，该参数对应的分配内存是每连接独享。对表进行顺序扫描的请求将分配一个读入缓冲区。
read_rnd_buffer_size = 160M # 随机读缓冲区大小，connection级参数，该参数对应的分配内存是每连接独享。默认值256KB，最大值4GB。当按任意顺序读取行时，将分配一个随机读缓存区。
join_buffer_size = 320M # 联合查询缓冲区大小，connection级参数，该参数对应的分配内存是每连接独享。
bulk_insert_buffer_size = 64M   # 批量插入数据缓存大小，可以有效提高插入效率，默认为8M
thread_cache_size = 8
# 服务器线程缓冲池中存放的最大连接线程数。默认值是8，断开连接时如果缓存中还有空间，客户端的线程将被放到缓存中，当线程重新被请求，将先从缓存中读取。
# 根据物理内存设置规则如下：1G  —> 8，2G  —> 16，3G  —> 32，大于3G  —> 64

thread_stack = 256K # 每个连接被创建时,mysql分配给它的内存。默认192KB，已满足大部分场景，除非必要否则不要动它，可设置范围128KB~4GB。
query_cache_type = 0 # 关闭查询缓存
query_cache_size = 0 # 查询缓存大小，在高并发，写入量大的系统，建议把该功能禁掉。
query_cache_limit = 4M    # 指定单个查询能够使用的缓冲区大小，缺省为1M
tmp_table_size = 1024M
# MySQL的heap（堆积）表缓冲大小，也即内存临时表，默认大小是 32M。如果超过该值，则会将临时表写入磁盘。在频繁做很多高级 GROUP BY 查询的DW环境，增大该值。
# 实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。

max_heap_table_size = 1024M # 用户可以创建的内存表(memory table)的大小，这个值用来计算内存表的最大行数值。
table_definition_cache = 400 # 表定义缓存区，缓存frm文件。表定义(global)是全局的，可以被所有连接有效的共享。
table_open_cache = 1000 # 所有SQL线程可以打开表缓存的数量，缓存ibd/MYI/MYD文件。 打开的表(session级别)是每个线程，每个表使用。
table_open_cache_instances = 4 # 对table cache 能拆成的分区数，用于减少锁竞争，最大值64.

########################################
#                                      #
#               Safety                 #
#                                      #
########################################
#sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
# MySQL支持的SQL语法模式，与其他异构数据库之间进行数据迁移时，SQL Mode组合模式会有帮助。

local_infile = OFF # 禁用LOAD DATA LOCAL命令
plugin-load = validate_password.so # 加密认证插件，强制mysql设置复杂密码
skip-name-resolve # 禁止MySQL对外部连接进行DNS解析，消除MySQL进行DNS解析。如果开启该选项，所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！

skip-external-locking
#skip-locking 
# 避免MySQL的外部锁定，减少出错几率，增强稳定性。

#skip-networking 
# 不允许CP/IP连接，只能通过命名管道（Named Pipes）、共享内存（Shared Memory）或Unix套接字（Socket）文件连接。
# 如果Web服务器以远程连接方式访问MySQL数据库服务器，则不要开启该选项，否则无法正常连接！
# 适合应用和数据库共用一台服务器的情况，其他客户端无法通过网络远程访问数据库

########################################
#                                      #
#                 Logs                 #
#                                      #
########################################
###################   General Log  ######################
general_log = OFF # 关闭通用查询日志
general_log_file = /var/log/mysql/general.log # 通用查询日志存放路径

###################     Slow Log   ######################
slow_query_log = ON # 开启慢查询日志
slow_query_log_file = /data/mysql/my3306/slow.log # 慢查询日志存放路径
long_query_time = 10 # 超过10秒的查询，记录到慢查询日志，默认值10
log_queries_not_using_indexes = ON # 没有使用索引的查询，记录到慢查询日志，打开可能引起慢查询日志快速增长
log_slow_admin_statements = ON
# 执行缓慢的管理语句，记录到慢查询日志
# 例如 ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.

###################     Error Log   ####################
log_error = /var/log/mysql/error.log # 错误日志存放路径
log_error_verbosity = 2
# 全局动态变量，默认3，范围：1～3
# 表示错误日志记录的信息，1：只记录error信息；2：记录error和warnings信息；3：记录error、warnings和普通的notes信息

########################################
#                                      #
#           Replication                #
#                                      #
########################################

###################     Bin Log    ######################
server_id = 1 # 数据库服务器ID
log_bin = /var/log/mysql/binlog # 二进制日志存放路径
log_bin_index = /var/log/mysql/binlog.index # 同binlog，定义binlog的位置和名称
binlog_format = row # binlog格式，复制有3种模式STATEMENT，ROW，MIXED
expire_logs_days = 10 # 只保留最近10天的binlog日志
max_binlog_size = 50M # 每个binlog日志文件的最大容量
binlog_cache_size = 2M
# 每个session分配的binlog缓存大小
# 事务提交前产生的日志，记录到Cache中；事务提交后，则把日志持久化到磁盘

log_slave_updates = ON # 开启log_slave_updates，从库的更新操作记录进binlog日志

sync_binlog = 1
# sync_binlog=0（默认），事务提交后MySQL不刷新binlog_cache到磁盘，而让Filesystem自行决定，或者cache满了才同步。
# sync_binlog=n，每进行n次事务提交之后，MySQL将binlog_cache中的数据强制写入磁盘。

binlog_rows_query_log_events = ON # 将row模式下的sql语句，记录到binlog日志，默认是0(off)

###################     Relay Log  ######################
relay_log = /var/log/mysql/relaylog # 中继日志存放路径
relay_log_index = /data/mysql/my3306/relaylog.index # 同relay_log，定义relay_log的位置和名称
#binlog_checksum = CRC32 # Session-Thread把Event写到Binlog时，生成checksum。默认为（NONE），兼容旧版本mysql。
master_verify_checksum = ON # Dump-Thread读Binlog中的Event时，验证checksum
slave_sql_verify_checksum = ON # 从库的I/O-Thread把Event写入Relaylog时，生成checksum；从库的SQL-Thread从Relaylog读Event时，验证checksum
master_info_repository = TABLE
relay_log_info_repository = TABLE # 将master.info和relay.info保存在表中，默认是Myisam引擎，官方建议改为Innodb引擎，防止表损坏后自行修复。 

relay_log_purge = ON 
relay_log_recovery = ON
# 启用relaylog的自动修复功能，避免由于网络之类的外因造成日志损坏，主从停止。

skip_slave_start = OFF # 重启数据库，复制进程默认不启动
slave_net_timeout = 5 # 当master和slave之间的网络中断，slave的I/O-Thread等待5秒，重连master
sync_master_info = 10000 # slave更新mysql.slave_master_info表的时间间隔

sync_relay_log = 10000
sync_relay_log_info = 10000
# slave更新mysql.slave_relay_log_info表的时间间隔

gtid_mode = ON
enforce_gtid_consistency = ON
# GTID即全局事务ID（global transaction identifier），GTID由UUID+TID组成的。
# UUID是一个MySQL实例的唯一标识，TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。
# GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。下面是一个GTID的具体形式：
# 4e659069-3cd8-11e5-9a49-001c4270714e:1-77

auto_increment_offset  = 1 # 双主复制中，2台服务器的自增长字段初值分别配置为1和2，取值范围是1 .. 65535
auto_increment_increment = 2 # 双主复制中，2台服务器的自增长字段的每次递增值都配置为2，其默认值是1，取值范围是1 .. 65535

########################################
#                                      #
#                InnoDB                #
#                                      #
########################################

innodb_data_home_dir = /usr/local/mysql/data # innodb表的数据文件目录
innodb_file_per_table = ON # 使用独立表空间管理
innodb_data_file_path = ibdata1:1G:autoextend # InnoDB共享表空间磁盘文件，存放数据字典、和在线重做日志
innodb_log_group_home_dir = /usr/loaca/mysql/data # 在事务被提交并写入到表空间磁盘文件上之前，事务数据存储在InnoDB的redo日志文件里。这些日志位于innodb_log_group_home_dir变量定义的目录中
innodb_buffer_pool_size = 2G # InnoDB用于缓存数据、索引、锁、插入缓冲、数据字典的缓冲池。该值越大，缓存命中率越高，但是过大会导致页交换。

innodb_buffer_pool_instances = 8
# 开启8个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写，降低并发导致的内部缓存访问冲突。
# InnoDB缓存系统会把参数innodb_buffer_pool_size指定大小的缓存，平分为innodb_buffer_pool_instances个buffer_pool

#innodb_additional_mem_pool_size = 16M
# InnoDB存储数据字典、内部数据结构的缓冲池大小，类似于Oracle的library cache

innodb_log_file_size = 256M
# InnoDB redo log大小，对应于ib_logfile0文件。
# ib_logfile* 是Innodb多版本缓冲的一个保证，该日志记录redo、undo信息，即commit之前的数据，用于rollback操作。
# 官方文档的建议设置是innodb_log_file_size = innodb_buffer_pool_size/innodb_log_files_in_group

innodb_log_buffer_size = 64M # redo日志所用的内存缓冲区大小
innodb_log_files_in_group = 4 # redo日志文件数，默认值为2，日志是以顺序的方式写入。

innodb_max_dirty_pages_pct = 90
# 缓存池中脏页的最大比例，默认值是75%，如果脏页的数量达到或超过该值，InnoDB的后台线程将开始缓存刷新。
# “缓存刷新”是指InnoDB在找不到干净的可用缓存页或检查点被触发等情况下，InnoDB的后台线程就开始把“脏的缓存页”回写到磁盘文件中。

innodb_flush_log_at_trx_commit = 1
#设置为0 ，每秒 write cache & flush disk
#设置为1 ，每次commit都 write cache & flush disk
#设置为2 ，每次commit都 write cache，然后根据innodb_flush_log_at_timeout（默认为1s）时间 flush disk

innodb_lock_wait_timeout = 10
# InnoDB 有其内置的死锁检测机制，能导致未完成的事务回滚。但是，如果结合InnoDB使用MyISAM的lock tables语句或第三方事务引擎,则InnoDB无法识别死锁。
# 为消除这种可能性，可以将innodb_lock_wait_timeout设置为一个整数值，指示MySQL在允许其他事务修改那些最终受事务回滚的数据之前要等待多长时间(秒数)。

innodb_sync_spin_loops = 40
# 自旋锁的轮转数，可以通过show engine innodb status来查看。
# 如果看到大量的自旋等待和自旋轮转，则它浪费了很多cpu资源。浪费cpu时间和无谓的上下文切换之间可以通过该值来平衡。

innodb_support_xa = ON
# 第一，支持多实例分布式事务（外部xa事务），这个一般在分布式数据库环境中用得较多。
# 第二，支持内部xa事务，即支持binlog与innodb redo log之间数据一致性。

#innodb_file_format = barracuda
# InnoDB文件格式，Antelope是innodb-base的文件格式，Barracude是innodb-plugin后引入的文件格式，同时Barracude也支持Antelope文件格式。

innodb_flush_method = O_DIRECT
# 设置innodb数据文件及redo log的打开、刷写模式，fdatasync(默认)，O_DSYNC，O_DIRECT
# 默认是fdatasync，调用fsync()去刷数据文件与redo log的buffer
# 设置为为O_DSYNC时，innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件
# 设置为O_DIRECT时，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log

innodb_strict_mode = OFF # 开启InnoDB严格检查模式，在某些情况下返回errors而不是warnings，默认值是OFF
innodb_checksum_algorithm = strict_crc32 # checksum函数的算法，默认为crc32。可以设置的值有:innodb、crc32、none、strict_innodb、strict_crc32、strict_none
innodb_status_file = 1 # 启用InnoDB的status file，便于管理员查看以及监控
innodb_open_files = 3000 # 限制Innodb能打开的表的数据，默认为300，数据库里的表特别多的情况，可以适当增大为1000。  
innodb_thread_concurrency = 4 # 同时在Innodb内核中处理的线程数量。服务器有几个CPU就设置为几，建议默认值。
innodb_thread_sleep_delay = 500
#innodb_file_io_threads = 16 # 文件读写I/O数，这个参数只在Windows上起作用。在LINUX上只会等于４，默认即可。
innodb_read_io_threads = 16 # 设置read thread(读线程个数，默认是4个)
innodb_write_io_threads = 16 # 设置write thread(写线程个数，默认是4个)
innodb_io_capacity = 2000 # 磁盘io的吞吐量，默认值是200.对于刷新到磁盘页的数量，会按照inodb_io_capacity的百分比来进行控制。
log_bin_trust_function_creators = 1 # 开启log-bin后可以随意创建function，存在潜在的数据安全问题。
innodb_purge_threads = 1 # 使用独立线程进行purge操作。每次DML操作都会生成Undo页，系统需要定期对这些undo页进行清理，这称为purge操作。
innodb_purge_batch_size = 32 # 在进行full purge时，回收Undo页的个数，默认是20，可以适当加大。

innodb_old_blocks_pct = 75
# LRU算法，默认值是37，插入到LRU列表端的37%，差不多3/8的位置。
# innodb把midpoint之后的列表称为old列表，之前的列表称为new列表，可以理解为new列表中的页都是最为活跃的热点数据。

innodb_change_buffering = all
# 用来开启各种Buffer的选项。该参数可选的值为：inserts、deletes、purges、changes、all、none。
# changes表示启用inserts和deletes，all表示启用所有，none表示都不启用。该参数默认值为all。

[mysqldump]                        
max_allowed_packet = 256M
quick # mysqldump导出大表时很有用，强制从服务器查询取得记录直接输出，而不是取得所有记录后将它们缓存到内存中。

[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
ledir = /usr/local/mysql/bin
# 包含mysqld程序的软件安装路径，用该选项来显式表示服务器位置。

mysql主从复制

一、主从复制的相关概念

1、主从复制原理流程

主节点必须启用二进制日志，记录任何修改数据库数据的事件。
从节点开启一个线程（I/O Thread)把自己扮演成mysql的客户端，通过mysql协议，请求主节点的二进制日志文件中的事件
主节点启动一个线程（dump Thread），检查自己二进制日志中的事件，跟对方请求的位置对比，如果不带请求位置参数，则主节点就会从第一个日志文件中的第一个事件一个一个发送给从节点。
从节点接收到主节点发送过来的数据把它放置到中继日志（Relay log）文件中。并记录该次请求到主节点的具哪个二进制日志文件的哪个位置。
从节点启动另外一个线程（sql Thread ），把replaylog中的事件读取出来，并在本地再执行一次。

2、MySQL支持的复制类型

基于语句的复制。在主服务器上执行的 SQL语句，在从服务器上执行同样的语句。配置：binlog_format = 'STATEMENT'
基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从 MySQL 5.0开始支持，配置：binlog_format = 'ROW'
混合类型的复制。默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制,配置：binlog_format = 'MIXED'

二、主从复制的常用架构设计

1、一主多从复制架构场景：在主库读取请求压力非常大的场景下，可以通过配置一主多从复制架构实现读写分离，把大量对实时性要求不是特别高的读请求通过负载均衡到多个从库上，降低主库的读取压力。在主库出现异常宕机的情况下，可以把一个从库切换为主库继续提供服务；
建议：
当 Slave 增加到一定数量时，Slave 对 Master 的负载以及网络带宽都会成为一个严重的问题。
不同的 Slave 扮演不同的作用(例如使用不同的索引，或者不同的存储引擎)
用一个 Slave 作为备用 Master，只进行复制
用一个远程的 Slave，用于灾难恢复。
2、多级复制架构场景：一主多从的架构能够解决大部分读请求压力特别大的场景需求，但主库的I/O压力和网络压力会随着从库的增加而增长，而使用多级复制架构就可以解决一主多从场景下，主库额外的I/O和网络压力。但要注意的是，多级复制场景下主库的数据是经历两次才到达读取的从库，期间的延时比一主多从复制场景下只经历一次复制的要大。
建议：
可能存在延时较长的风险
这种方案可以与第三方软件结合使用，例如Slave+LVS+Keepalived 实现高可用。
3、双主复制/Dual Master架构
场景：双主/Dual Master架构适用于写压力比较大的场景，或者DBA做维护需要主从切换的场景，通过双主/Dual master架构避免了重复搭建从库的麻烦。
建议：
最大问题就是更新冲突。
可以采用MySQL Cluster，以及将Cluster和Replication结合起来，可以建立强大的高性能的数据库平台。

三、配置过程

主节点：

启用二进制日志。
为当前节点设置一个全局唯一的server_id。
创建有复制权限的用户账号 REPLIACTION SLAVE ,REPLIATION CLIENT。

从节点：

启动中继日志。
为当前节点设置一个全局唯一的server_id。
配置访问主节点的参数信息（CHANG MASTER）
使用有复制权限的用户账号连接至主节点，并启动复制线程。

#主服务器
#修改配置文件
vim /etc/my.cnf
server-id=1
添加：log-bin = mysql-bin
添加 :server-id =1
添加：innodb-file-per-table =ON
添加：skip_name_resolve=ON
添加：expire_logs_days=7
增加 :binlog_format=mixed

#登陆创建授权用户
mysql>grant replication slave,replication clinet on *.* to 'replication'@'192.168.124.10(slave IP)' identified by '123456';
mysql>flush privileges
#拷贝数据
mysql>flush tables with read lock;
mysqldump -uroot -p1234 dy_qqopen > dy_qqopen.sql
mysql>unlock tables;

#从服务器
#修改配置文件
将 server-id=1 改成 server-id=2
relay-log=/var/log/mysql/relaylogs
#登陆配置连接信息选项
mysql>change master to master_host='192.168.124.20',master_user='replication',master_password='123456',master_log_file='master-bin.000001',master_log_pos=103;
mysql> start slave; #启动复制线程
#参考https://blog.csdn.net/daicooper/article/details/79905660

四、注意事项

1、配置从服务器为只读
配置文件中打开 read_only=ON
mysql -uroot -p123456 -e "FLUSH TABLES WETH READ LOCK;"
2、当从库同时做主库级联时，需要打开log_slave_updates参数，否则从库的binlog不会记录来源于主库的操作记录。
3、不能在mysql配置文件里直接写入master的配置信息，需要用change master命令来完成
4、如果不及时清理，日积月累二进制日志文件可能会把磁盘空间占满，可以在主库配置文件里加上expire_logs_days=7，只保留最近7天的日志，建议当slave不再使用时，通过从库配置relay_log_purge=1 (打开自动清除中继日志)

数据库进阶

非关系型数据库（适应web2.0时期用户动态请求高并发场景）

键值对形式存储（key-value）

典型产品：memcached（纯内存） redis

面向文档数据库：数据以文档形式存储，漱口可以使用xml、json等多种形式存储

典型产品：mongodb

数据库恢复

mysqlbinlog数据恢复
1、本质就是:使用mysqlbinlog命令，解析读取binlog二进制文件中，所记录的sql语句操作记录数据
2、通过指定--start-position\--stop-position\--start-datetime\--stop-datetime等参数，来限定需要解析读取的sql语句区间（时间区间、位置区间）

案例
#根据binlog查找对应表的数据进行恢复

mysqlbinlog --start-datetime="2021-07-20 08:00:00" --stop-datetime="2021-07-27 08:25:00" /var/lib/mysql/mysql-bin.000018 -d npx_db_14 | grep "npx_device" -A 1 > npx_device2.sql // 查找需要的insert语句整理至binlogback2.sql,-A 1 为了保留每行语句中下一行的;号

# 按照顺序执行生成的sql文件即可

mysqlbinlog --start-position='3380' --stop-position='15751' /var/lib/mysql/mysql-bin.000015 | mysql -uroot -pniceuc

posted @ 2018-11-26 15:43 he_ding 阅读(315) 评论(0) 收藏举报

刷新页面返回顶部

$he_ding%

星光不问赶路人，时光不负有心人