mysql安装部署手册

mysql下载地址

各版本安装包下载

https://dev.mysql.com/downloads/mysql/

yum安装部署

步骤1:安装对应的yum源程序包

实际最新的版本请登录官网查看
源地址: https://dev.mysql.com/downloads/repo/yum/

#必须先下载安装yum安装包,然后才能进行yum安装部署，根据实际情况选择对应的版本
[root@localhost ~]# curl -O https://repo.mysql.com/mysql80-community-release-el7-9.noarch.rpm
[root@localhost ~]# curl -O https://repo.mysql.com/mysql84-community-release-el7-1.noarch.rpm
#安装yum源包,根据实际情况选择对应的版本
[root@localhost ~]# rpm -ivh mysql80-community-release-el7-9.noarch.rpm

步骤2:启用对应的源

方式1:

#选择对应的版本 禁用8版本，启用5.7版本
#方式1：有个弊端，就是每次版本更新后软件的名字可能不一样，推荐使用方式2
#mysql80-community和mysql57-community 名字可能不对，所以推荐方式2
yum -y install yum-utils
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community
#选择对应的版本 禁用5.7版本，启用8版本
yum-config-manager --enable mysql80-community 
yum-config-manager --disable mysql57-community

#查看已安装的MySQL源
[root@localhost ~]# yum repolist all | grep mysql
mysql-cluster-7.5-community/x86_64                  MySQL Cluster 7 禁用
mysql-cluster-7.5-community-source                  MySQL Cluster 7 禁用
mysql-cluster-7.6-community/x86_64                  MySQL Cluster 7 禁用
mysql-cluster-7.6-community-source                  MySQL Cluster 7 禁用
mysql-cluster-8.0-community/x86_64                  MySQL Cluster 8 禁用
mysql-cluster-8.0-community-debuginfo/x86_64        MySQL Cluster 8 禁用
mysql-cluster-8.0-community-source                  MySQL Cluster 8 禁用
mysql-cluster-innovation-community/x86_64           MySQL Cluster I 禁用
mysql-cluster-innovation-community-debuginfo/x86_64 MySQL Cluster I 禁用
mysql-cluster-innovation-community-source           MySQL Cluster I 禁用
mysql-connectors-community/x86_64                   MySQL Connector 启用:    250
mysql-connectors-community-debuginfo/x86_64         MySQL Connector 禁用
mysql-connectors-community-source                   MySQL Connector 禁用
mysql-innovation-community/x86_64                   MySQL Innovatio 禁用
mysql-innovation-community-debuginfo/x86_64         MySQL Innovatio 禁用
mysql-innovation-community-source                   MySQL Innovatio 禁用
mysql-tools-community/x86_64                        MySQL Tools Com 启用:    106
mysql-tools-community-debuginfo/x86_64              MySQL Tools Com 禁用
mysql-tools-community-source                        MySQL Tools Com 禁用
mysql-tools-innovation-community/x86_64             MySQL Tools Inn 禁用
mysql-tools-innovation-community-debuginfo/x86_64   MySQL Tools Inn 禁用
mysql-tools-innovation-community-source             MySQL Tools Inn 禁用
mysql-tools-preview/x86_64                          MySQL Tools Pre 禁用
mysql-tools-preview-source                          MySQL Tools Pre 禁用
mysql57-community/x86_64                            MySQL 5.7 Commu 启用:    696
mysql57-community-source                            MySQL 5.7 Commu 禁用
mysql80-community/x86_64                            MySQL 8.0 Commu 禁用
mysql80-community-debuginfo/x86_64                  MySQL 8.0 Commu 禁用
mysql80-community-source                            MySQL 8.0 Commu 禁用

#查看已启用的mysql源
[root@localhost ~]# yum repolist enabled | grep mysql
mysql-connectors-community/x86_64       MySQL Connectors Community           250
mysql-tools-community/x86_64            MySQL Tools Community                106
mysql57-community/x86_64                MySQL 5.7 Community Server           696

方式2:如果使用此方式，就不需要单独下载yum源安装包了，直接编辑配置文件即可

#方式2:
或者用下面的方式也行
#除了使用yum-config-manager选择使用版本，还可以修改以下配置文件进行修改
#安装完成后会在/etc/yum.repos.d/下生成两个主要yum源文件
mysql-community.repo 
mysql-community-source.repo
根据实际情况选择对应的版本
把enabled=0 表示禁用 1 表示启用

#第三个文件可以不用管mysql-community-debuginfo.repo

文件1:mysql-community.repo

vim /etc/yum.repos.d/mysql-community.repo

# Enable to use MySQL 5.7
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-innovation-community]
name=MySQL Innovation Release Community Server
baseurl=http://repo.mysql.com/yum/mysql-innovation-community/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022

[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-innovation-community]
name=MySQL Tools Innovation Community
baseurl=http://repo.mysql.com/yum/mysql-tools-innovation-community/el/7/$basearch/
enabled=0
gpgcheck=1

[mysql-tools-preview]
name=MySQL Tools Preview
baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-7.5-community]
name=MySQL Cluster 7.5 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.5-community/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-7.6-community]
name=MySQL Cluster 7.6 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.6-community/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-8.0-community]
name=MySQL Cluster 8.0 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-8.0-community/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-innovation-community]
name=MySQL Cluster Innovation Release Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-innovation-community/el/7/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022

文件2:mysql-community-source.repo

vim /etc/yum.repos.d/mysql-community-source.repo

[mysql57-community-source]
name=MySQL 5.7 Community Server - Source
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql80-community-source]
name=MySQL 8.0 Community Server - Source
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-innovation-community-source]
name=MySQL Innovation Release Community Server - Source
baseurl=http://repo.mysql.com/yum/mysql-innovation-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022

[mysql-connectors-community-source]
name=MySQL Connectors Community - Source
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community-source]
name=MySQL Tools Community - Source
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-innovation-community-source]
name=MySQL Tools Innovation Community - Source
baseurl=http://repo.mysql.com/yum/mysql-tools-innovation-community/el/7/SRPMS
enabled=0
gpgcheck=1

[mysql-tools-preview-source]
name=MySQL Tools Preview - Source
baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-7.5-community-source]
name=MySQL Cluster 7.5 Community - Source
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.5-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-7.6-community-source]
name=MySQL Cluster 7.6 Community - Source
baseurl=http://repo.mysql.com/yum/mysql-cluster-7.6-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-8.0-community-source]
name=MySQL Cluster 8.0 Community - Source
baseurl=http://repo.mysql.com/yum/mysql-cluster-8.0-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-innovation-community-source]
name=MySQL Cluster Innovation Release Community - Source
baseurl=http://repo.mysql.com/yum/mysql-cluster-innovation-community/el/7/SRPMS
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022

文件3:mysql-community-debuginfo.repo

这个文件可以不写也没事

vim /etc/yum.repos.d/mysql-community-debuginfo.repo

[mysql80-community-debuginfo]
name=MySQL 8.0 Community Server - Debuginfo
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/debuginfo/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-innovation-community-debuginfo]
name=MySQL Innovation Release Community Server - Debuginfo
baseurl=http://repo.mysql.com/yum/mysql-innovation-community/el/7/debuginfo/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022

[mysql-connectors-community-debuginfo]
name=MySQL Connectors Community - Debuginfo
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/debuginfo/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community-debuginfo]
name=MySQL Tools Community - Debuginfo
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/debuginfo/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-innovation-community-debuginfo]
name=MySQL Tools Innovation Community - Debuginfo
baseurl=http://repo.mysql.com/yum/mysql-tools-innovation-community/el/7/debuginfo/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022

[mysql-cluster-8.0-community-debuginfo]
name=MySQL Cluster 8.0 Community - Debuginfo
baseurl=http://repo.mysql.com/yum/mysql-cluster-8.0-community/el/7/debuginfo/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-cluster-innovation-community-debuginfo]
name=MySQL Cluster Innovation Release Community - Debuginfo
baseurl=http://repo.mysql.com/yum/mysql-cluster-innovation-community/el/7/debuginfo/$basearch
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022

步骤3:yum在线安装

yum install mysql-community-server -y

yum在线下载不安装

• 在线只下载包不安装,目的用于后续离线环境版本安装使用.

mkdir -p /root/mysql
yum -y install mysql-community-server --downloadonly --downloaddir=/root/mysql/

yum在线版本升级

#方式1
dnf --refresh upgrade mysql-server
#方式2
yum update mysql-server

相关注意事项

• 禁用默认的MySQL模块

（仅限EL8系统）基于RHEL8和Oracle Linux 8的基于EL8的系统包括默认情况下启用的MySQL模块。除非禁用此模块，否则它将屏蔽MySQL存储库提供的软件包。要禁用包含的模块并使MySQL存储库软件包可见，请使用以下命令（对于启用dnf的系统，将命令中的yum替换 为 dnf）：

yum module disable mysql -y

• 保护MySQL安装（仅适用于MySQL 5.6）

程序mysql_secure_installation 允许您执行重要的操作，例如设置root密码，删除匿名用户等。始终运行它以确保您的MySQL 5.6安装安全：
安装MySQL 5.7或更高版本后， 请勿运行mysql_secure_installation，因为该程序的功能已由Yum存储库安装完成。

shell> mysql_secure_installation

数据库服务启停

-- 6版本
service mysqld start 
service mysqld status 
chkconfig --list mysqld 
chkconfig mysqld on
-- 7版本
systemctl enable mysqld.service
systemctl stop mysqld.service
systemctl start mysqld.service 
systemctl restart mysqld.service 
systemctl status mysqld.service
systemctl disable mysqld.service

待梳理

数据文件存放路径

mysql> show variables like '%datadir%';
+---------------+-------------------+
| Variable_name | Value             |
+---------------+-------------------+
| datadir       | /mysqldata/mysql/ |
+---------------+-------------------+
1 row in set (0.00 sec)
 

时间

将日期时间值格式化为指定的字符串
SELECT DATE_FORMAT('2023-03-11 15:32:45', '%Y-%m-%d %H:%i:%s');
这将返回日期时间值的格式化字符串，即：2023-03-11 15:32:45

返回当前日期和时间的值
mysql> select now();

从日期时间值中提取日期部分
SELECT DATE('2023-03-11 15:32:45');
这将返回日期部分的值，即：2023-03-11

从日期时间值中提取时间部分
SELECT TIME('2023-03-11 15:32:45');
这将返回时间部分的值，即：15:32:45

从日期时间值中提取年份部分：
SELECT YEAR('2023-03-11 15:32:45');
这将返回年份部分的值，即：2023

从日期时间值中提取月份部分：
SELECT MONTH('2023-03-11 15:32:45');
这将返回月份部分的值，即：3

从日期时间值中提取天数部分：
SELECT DAY('2023-03-11 15:32:45');
这将返回天数部分的值，即：11

从日期时间值中提取小时部分：
SELECT HOUR('2023-03-11 15:32:45');
这将返回小时部分的值，即：15

从日期时间值中提取分钟部分：
SELECT MINUTE('2023-03-11 15:32:45');
这将返回分钟部分的值，即：32

从日期时间值中提取秒数部分：
SELECT SECOND('2023-03-11 15:32:45');
这将返回秒数部分的值，即：45

返回当前日期
SELECT CURDATE();
这将当前日期，即：2023-03-11

返回当前时间戳
SELECT UNIX_TIMESTAMP();
这将返回当前时间戳的值，例如：1647045165

将时间戳转换为日期时间值
SELECT FROM_UNIXTIME(1647045165);
这将返回时间戳对应的日期时间值，例如：2023-03-11 15:39:25



将时间值格式化为指定的字符串格式
SELECT TIME_FORMAT('15:32:45', '%H:%i:%s');
这将返回时间值的格式化字符串，即：15:32:45
PS：可以在TIME_FORMAT函数的第二个参数中指定所需的格式。

将字符串转换为日期时间值
SELECT STR_TO_DATE('2023-03-11', '%Y-%m-%d');
这将返回字符串对应的日期时间值，即：2023-03-11

返回给定日期的星期几
SELECT WEEKDAY('2023-03-11');
这将返回给定日期的星期几的值，例如：5（表示星期六，周一为0）

返回给定日期的星期几的名称
SELECT DAYNAME('2023-03-11');
这将返回给定日期的星期几的名称，即返回"Saturday"

返回给定日期的月份的名称
SELECT MONTHNAME('2023-03-11');
这将返回给定日期的月份的名称，例如：March

返回给定日期的年份和周数
SELECT YEARWEEK('2023-03-11');
这将返回给定日期的年份和周数的值，例如：202310

计算两个时间值之间的时间差
SELECT TIMEDIFF('2023-03-11 16:00:00', '2023-03-11 15:00:00');
这将返回两个时间值之间的时间差，例如：01:00:00

计算两个日期时间值之间的时间差，以指定的时间单位返回结果
SELECT TIMESTAMPDIFF(HOUR, '2023-03-11 15:00:00', '2023-03-11 16:00:00');

这将返回两个时间值之间的小时数，即：1

返回当前UTC日期
SELECT UTC_DATE();
这将返回当前UTC日期的值，例如：2023-03-11

返回当前UTC时间
SELECT UTC_TIME();
这将返回当前UTC时间的值，例如：15:32:45

返回当前UTC日期和时间
SELECT UTC_TIMESTAMP();
这将返回当前UTC日期和时间的值，例如：2023-03-11 07:32:45

将秒数转换为时间值
SELECT SEC_TO_TIME(3600);
这将返回秒数对应的时间值，即：01:00:00

将时间值转换为秒数
SELECT TIME_TO_SEC('01:00:00');
这将返回时间值对应的秒数，即：3600

获取指定时间的当月的最后一天
SELECT LAST_DAY(DATE('2023-03-11  16:00:03'));
返回的结果为“2023-03-31”

将日期加上指定的天数
SELECT ADDDATE('2023-03-11', INTERVAL 7 DAY);
这将返回日期加上7天后的值，即：2023-03-18

将日期减去指定的天数
SELECT SUBDATE('2023-03-11', INTERVAL 7 DAY);
这将返回日期减去7天后的值，即：2023-03-04

计算两个日期之间的天数差
SELECT DATEDIFF('2023-03-11', '2023-03-04');
这将返回两个日期之间的天数差，即：7

计算两个时间之间的时间差
SELECT TIMEDIFF('23:59:59', '12:00:00');
这将返回两个时间之间的时间差，即：11:59:59

计算两个日期之间的差
SELECT TIMESTAMPDIFF(DAY, '2023-03-04', '2023-03-11');
这将返回两个日期之间的天数差，即：7。可以在TIMESTAMPDIFF函数的第一个参数中指定所需的时间单位

计算当前月份的第一天和最后一天
SELECT DATE_FORMAT(NOW(), '%Y-%m-01') AS first_day_of_month, LAST_DAY(NOW()) AS last_day_of_month;
这将返回当前月份的第一天和最后一天的值，例如：2023-03-01和2023-03-31

计算指定月份的第一天和最后一天
SELECT DATE_FORMAT('2023-06-15', '%Y-%m-01') AS first_day_of_month, LAST_DAY('2023-06-15') AS last_day_of_month;
这将返回指定月份的第一天和最后一天的值，例如：2023-06-01和2023-06-30

计算当前周的第一天和最后一天
SELECT DATE_FORMAT(DATE_ADD(NOW(), INTERVAL(1-DAYOFWEEK(NOW())) DAY), '%Y-%m-%d') AS first_day_of_week, DATE_FORMAT(DATE_ADD(NOW(), INTERVAL(7-DAYOFWEEK(NOW())) DAY), '%Y-%m-%d') AS last_day_of_week;
这将返回当前周的第一天和最后一天的值（默认周日为每周的第一天），例如：2023-03-05和2023-03-11

计算指定日期所在周的第一天和最后一天：
SELECT DATE_FORMAT(DATE_ADD('2023-06-15', INTERVAL(1-DAYOFWEEK('2023-06-15')) DAY), '%Y-%m-%d') AS first_day_of_week, DATE_FORMAT(DATE_ADD('2023-06-15', INTERVAL(7-DAYOFWEEK('2023-06-15')) DAY), '%Y-%m-%d') AS last_day_of_week;
这将返回指定日期所在周的第一天和最后一天的值，例如：2023-06-11和2023-06-17

备份与恢复

mysqldump

备份库

#导出一个库,如果加了参数--databases，那么在恢复时很方便，直接恢复无需先创建数据库及用户
#如果不加databases参数，那么在导入数据库时需要先把数据库创建好，建议添加上
#目前在用的备份方式
$>mysqldump --defaults-extra-file=/etc/my.cnf --databases wordpress > wordpress2023.sql
#导出的备份文件就是数据库脚本文件
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql
#导出所有数据库
$> mysqldump --all-databases > dump.sql
$> mysqldump -uroot -proot -A > all_database.sql
#导出多个库(生产建议一个库一个库的导出)
$> mysqldump -uroot -proot --databases db1 db2 db3  > dump.sql

备份表

#导出数据库中某个表
导出pzz数据库中student表中的所有数据
mysqldump -uroot -proot pzz student > student.sql
导出pzz数据库中表结构相关数据
mysqldump -uroot -proot -d pzz > pzz_table.sql
导出pzz数据库中除了表结构之外的实际数据
mysqldump -uroot -proot -t pzz > pzz_data.sql

#参数解释:
 -u：表示指定登录数据库使用的用户。
 -p：表示指定登录数据库使用的用户密码。
 -d：表示导出时只导出数据库的表结构。
 -t：表示导出时只导出数据库的具体数据而不包括表结构。
 -A：表示导出所有的数据库。

注意事项

#小坑：mysqldump: [Warning] Using a password on the command line interface can be insecure

mysql自5.7版本开始，考虑到mysql的安全性而更改对保护机制，在mysqldump备份数据库对时候不能直接在命令行上书写数据库的密码。

解决方式：
修改mysql的配置文件 vim /etc/my.cnf  文件
注意：主要是添加host user password 三个参数，必须填写在[client]模块下，如果my.cnf文件中没有该模块，手动填写一个(必须)，然后再下面补充数据库的三个配置信息

[client]
host = localhost
user = root
password = 'root'

恢复

#方式1：使用“<”符号  使用这个方式导入的话，前提必须是备份时使用参数--databases，见下面的小知识
#-e选项，用于指定连接 MySQL后执行的命令，命令执行完后自动退出
#先删除要导入的库
mysql -uroot -pwangjian -e 'drop database wordpress;'
mysql -uroot -pwangjian -e 'show databases;'
5.7之前写法
mysql -uroot -proot < exp.sql
5.7之后写法
mysql --defaults-extra-file=/etc/my.cnf < wordpress2023.sql
方式2：source命令
或者是登录进入数据库后，执行：
mysql> use wordpress;  --先切换到要导入的数据库
source /root/exp.sql
方式3：需要提前创建好数据库
mysqldump --defaults-extra-file=/etc/my.cnf wordpress < wordpress.sql
验证数据是否恢复成功，看恢复的表数量是否正确（库中有34个表）
[root@localhost ~]# mysql -uroot -pwangjian -e 'show tables from wordpress;' | awk 'NR>1 {print }' | wc -l
34
小知识：
如果转储文件是由 mysqldump 使用 --all-databases 或 --databases 选项创建的，则 包含 CREATE DATABASE 和 USE 语句，它不是需要指定要加载到的默认数据库数据：
$> mysql < dump.sql
或者
mysql> source dump.sql
如果该文件是不包含 CREATE DATABASE 和 USE 语句的单数据库转储，请创建数据库优先（如有必要）：
$> mysqladmin create db1
然后在加载转储文件时指定数据库名称：
$> mysql db1 < dump.sql
或者，从 mysql 中，创建 数据库，选择它作为默认数据库，然后加载转储文件：
mysql> CREATE DATABASE IF NOT EXISTS db1;
mysql> USE db1;
mysql> source dump.sql

相关说明

（一）按照备份对数据库的影响分类
1、热备份（Hot Backup）。 指在数据库运行过程中进行备份，并且对数据库正常运行没有任何影响。
2、冷备份（Cold Backup）。 指在数据库停止运行后开始进行的备份。
3、温备份（Warm Backup）。 指在数据库运行过程中进行备份，但是会造成数据库性能下降，对数据库提供服务造成影响的备份方式。

（二）按照备份的文件进行分类
1、逻辑备份。 采用逻辑备份的方式，备份出的数据通常是.sql类型的文件。备份后的内容可读且为文本文件。该方法一般用于数据库的升级、迁移或者对数据库表的批量修改等场景。该方法恢复时间较长。
2、裸文件备份。 指拷贝数据库的物理文件，采用这种方式，数据库恢复时间较短。

（三）按照备份的方式进行分类
1、完全备份。 指的是对数据库进行完整的备份。
2、增量备份。 指的是在上一次备份的基础上，对更新的数据进行备份，而不是备份所有数据。
3、日志备份。 指的是对数据库的日志进行备份，MySQL主从同步架构中就是采用这种备份

mysqlhotcopy裸文件导出

在MySQL5.5及以下版本的数据库中（MySQl5.7版本的数据库中已经删去mysqlhotcopy命令），可以进行快速备份。mysqlhotcopy所进行的备份，本质上就是对数据库库表文件的直接物理复制，只不过在复制时使用了锁对数据库的内容进行了锁定。并且，mysqlhotcopy命令只能备份MyISAM引擎的数据表。mysqlhotcopy命令使用示例如下：

是对pzz数据库进行备份
mysqlhotcopy -u root -p root pzz /root
是对pzz数据库中的student数据表进行备份
mysqlhotcopy -u root -p root pzz./student/root

mysqldump与mysqlhotcopy比较

1、mysqldump会备份成.sql文件，而mysqlhotcopy采用的是裸文件备份。
2、mysqldump备份和恢复比mysqlhotcopy速度慢，因此不适合大文件备份。
3、mysqldump支持MyISAM和InnoDB引擎，而mysqlhotcopy只支持MyISAM引擎。
4、mysqlhotcopy在MySQL5.5（不含）以上的版本中不自带。
5、mysqlhotcopy只能运行在数据库所在的设备上，而mysqldump可以运行在本地设备上，也可以运行在远程客户端。
6、mysqldump和mysqlhotcopy在运行时，都会对数据库进行上锁操作。
7、mysqldump备份的恢复本质上是对.sql文件中SQL语句的执行，而mysqlhotcopy备份的回复本质上是直接覆盖。

mysqlhotcopy导入数据

如果我们采用mysqlhotcopy对数据库进行备份，那么在恢复时只需要将备份的数据覆盖原有的数据即可。

日志

查看通用查询日志是否开启

mysql> show variables like 'general%';
+------------------+--------------------------------+
| Variable_name    | Value                          |
+------------------+--------------------------------+
| general_log      | OFF                            |
| general_log_file | /www/server/data/localhost.log |
+------------------+--------------------------------+
2 rows in set (0.01 sec)
如果只为off说明日志是关闭的  general_log_file值为日志路径

查看二进制日志是否开启

mysql> show variables like 'log_bin%';
+---------------------------------+----------------------------------+
| Variable_name                   | Value                            |
+---------------------------------+----------------------------------+
| log_bin                         | ON                               |
| log_bin_basename                | /www/server/data/mysql-bin       |
| log_bin_index                   | /www/server/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                              |
| log_bin_use_v1_row_events       | OFF                              |
+---------------------------------+----------------------------------+
5 rows in set (0.00 sec)
如果log_bin值为ON 说明开启了

每重启一次，服务就会生成一个新的二进制日志文件，后缀为1 2 3
[root@localhost ~]# cd /www/server/data/
[root@localhost data]# ls
auto.cnf         client-key.pem  ib_logfile1                mysql             mysql-bin.000004  performance_schema  server-key.pem
ca-key.pem       ib_buffer_pool  ibtmp1                     mysql-bin.000001  mysql-bin.000005  private_key.pem     sys
ca.pem           ibdata1         localhost.localdomain.err  mysql-bin.000002  mysql-bin.index   public_key.pem      wjdb
client-cert.pem  ib_logfile0     localhost.localdomain.pid  mysql-bin.000003  mysql-slow.log    server-cert.pem

查看慢查询日志功能是否开启

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            | ON                              |
| slow_query_log_file       | /www/server/data/mysql-slow.log |
+---------------------------+---------------------------------+
5 rows in set (0.00 sec)

看这个值slow_query_log 是否为ON
slow_query_log_file这个值为日志路径

表操作

查询表

查询库中有哪些表

show tables;

查询表所有数据

select * from 表名;

查询表中有多少条数据

select count(*) from 表名;

视图查询

SHOW FULL TABLES;(包括表和视图)
SHOW FULL TABLES WHERE table_type = 'VIEW';(单独视图查询)

表结构

desc 表名；

注释

查看表及字段有没有注释

mysql> show create table xs; #xs 是表名

--模糊查询 查询以K开头的表有多少
SHOW TABLES LIKE 'k%';

--查询以K结尾的表有多少
SHOW TABLES LIKE '%s';


--查询mysql库中以time开头的表(指定某个库)
SHOW TABLES FROM mysql LIKE 'time%';  或者 SHOW TABLES IN mysql LIKE 'time%';
请注意，如果您没有基表或视图的权限，则它不会显示在SHOW TABLES命令的结果集中。



#显示创建表SQL
SHOW CREATE TABLE wp_users ;

查询表中的不同行
SELECT DISTINCT c1 FROM t
 WHERE condition

按升序或降序对结果集排序
SELECT c1, c2 FROM t
 ORDER BY c1 ASC [DESC]

跳过行的偏移并返回下n行
SELECT c1, c2 FROM t
 ORDER BY c1 
LIMIT n OFFSET offset
使用聚合函数对行进行分组
SELECT c1, aggregate(c2)
 FROM t
 GROUP BY c1

使用HAVING子句筛选组
 SELECT c1, aggregate(c2)
 FROM t
 GROUP BY c1
 HAVING condition

创建表

--创建表，同时添加字段以及表的注释 
create table xuesheng(
    id int(10) primary key comment '主键',
    name varchar(255)  comment '学生姓名'
 )COMMENT='学生表';
create table teach(
    id int(10) primary key comment '主键',
    name varchar(255) comment '老师姓名'
)COMMENT='老师表';

插入数据

#方式1：指定字段插入，灵活 
#方式2：不写字段，但是需要把所有的值加上
insert into xuesheng (id,name) values(1,"张三");
insert into xuesheng values(2,"李四");

修改表

修改表名

mysql> alter table xuesheng rename to xs; #把xuesheng表名 ，需改为了xs

修改列名

向表中添加新列
ALTER TABLE t ADD column;

从表中删除列c
ALTER TABLE t DROP COLUMN c ;



将列c1重命名为c2
ALTER TABLE t1 RENAME c1 TO c2 ;

将列c1的数据类型改为datatype
ALTER TABLE t1 MODIFY c1 datatype;

--修改表的字符集为utf8 可能需要root用户，普通用户需要有权限
ALTER TABLE `wjdb`.`student` CHARACTER SET = utf8mb4;

备份和恢复

恢复表
当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。
mysql -u root -p -e 'drop table school.info1;'
mysql -u root -p -e 'show tables from school;'
mysql -u root -p school < /opt/school_info1.sql
mysql -u root -p -e 'show tables from school;'
验证数据是否恢复成功，看恢复的表的数据是否正确（查询wp_users表中有两条数据）
[root@localhost ~]# mysql -uroot -pwangjian -e 'select *  from wordpress.wp_users;' | awk 'NR>1 {print }' | wc -l
2

数据库集群安装

主从复制原理

主从复制原理，简言之，分三步曲进行：

• 主数据库有个bin log二进制文件，纪录了所有增删改SQL语句。（binlog线程）
• 从数据库把主数据库的bin log文件的SQL 语句复制到自己的中继日志 relay log（io线程）
• 从数据库的relay log重做日志文件，再执行一次这些sql语句。（Sql执行线程）

详细过程如下：

• 主库的更新SQL(update、insert、delete)被写到binlog
• 从库发起连接，连接到主库。
• 此时主库创建一个binlog dump thread，把bin log的内容发送到从库。
• 从库启动之后，创建一个I/O线程，读取主库传过来的bin log内容并写入到relay log
• 从库还会创建一个SQL线程，从relay log里面读取内容，从ExecMasterLog_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

主主 主从的区别

• 数据库主主：两台都是主数据库，同时对外提供读写操作。客户端访问任意一台。数据存在双向同步。
• 数据库主从：一台是主数据库，同时对外提供读写操作。一台是从数据库，对外提供读的操作。数据从主库同步到从库。

确保数据一致性

了解数据库的主从复制原理后，了解到从库拿到并执行主库的binlog日志，就可以保持数据与主库一致了。这是为什么呢？哪些情况会导致不一致呢？

长链接：

主库和从库在同步数据的过程中断怎么办呢，数据不就会丢失了嘛。因此主库与从库之间维持了一个长链接，主库内部有一个线程，专门服务于从库的这个长链接的。

binlog格式：

binlog日志有三种格式，分别是statement，row和mixed。如果是statement格式，binlog记录的是SQL的原文，如果主库和从库选的索引不一致，可能会导致主库不一致。我们来分析一下。假设主库执行删除这个SQL（其中a和create_time都有索引）如下：

delete from t where a > '666' and create_time<'2022-03-01' limit 1;

我们知道，数据选择了a索引和选择create_time索引，最后limit 1出来的数据一般是不一样的。所以就会存在这种情况：在binlog = statement格式时，主库在执行这条SQL时，使用的是索引a，而从库在执行这条SQL时，使用了索引create_time。最后主从数据不一致了。

解决这个问题的方法：可以把binlog格式修改为row。row格式的binlog日志，记录的不是SQL原文，而是两个event:Table_map 和 Delete_rows。Table_map event说明要操作的表，Delete_rows event用于定义要删除的行为，记录删除的具体行数。row格式的binlog记录的就是要删除的主键ID信息，因此不会出现主从不一致的问题。

binlog_format=row MySQL5.7.7版本之后，把binlog_format的默认值修改为了row

但是如果SQL删除10万行数据，使用row格式就会很占空间的，10万条数据都在binlog里面，写binlog的时候也很耗IO。但是statement格式的binlog可能会导致数据不一致，因此设计MySQL的大叔想了一个折中的方案，mixed格式的binlog。所谓的mixed格式其实就是row和statement格式混合使用，当MySQL判断可能数据不一致时，就用row格式，否则使用就用statement格式。

binlog格式设置：

STATEMENT模式（基于SQL语句的复制(statement-based replication, SBR)）
ROW模式（基于行的复制(row-based replication, RBR)）
MIXED模式（混合模式复制(mixed-based replication, MBR)）

其它检查

1、确保同步状态正常

主从数据库的同步状态正常是保证主从数据一致性的前提，需要定期监控主从同步状态，并及时处理同步异常情况。

2、配置和参数设置保持一致

主从数据库的配置和参数设置必须一致，否则可能导致主从数据不一致问题。可以通过检查my.cnf文件、SHOW VARIABLES命令等方式来确认配置和参数是否一致。

3、定期备份和比对数据

定期备份主从数据库数据，并进行比对，查看是否有差异。可以使用mysqldump工具或者其他自动化备份工具进行备份，并使用比对工具进行数据检查。

4、选择合适的数据同步方式

使用适当的数据同步方式能够更好地保证主从数据的一致性。例如，使用基于GTID或binlog格式的数据同步方式，可确保主从数据的同步流程更为精确和可靠。

设置主从

实例如下：

ip	端口	节点类型
192.168.70.128	3306	主节点
192.168.70.144	3306	从节点

首先各个节点安装同一个版本的mysql，我这以5.7.43安装为例，只有各个节点数据库安装完成后，才开始修改配置文件。

修改配置

主服务器修改

cp /etc/my.cnf /etc/my.cnf.bak
vim /etc/my.cnf
#服务id
server-id=1 
#开启binlog， master-bin是日志文件名的开头
log_bin=master-bin 
log_bin-index=master-bin.index
binlog_format=row
#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除
expire_logs_days=90
#注释bind-address配置, 否则只能本机登陆mysql
#bind-address=127.0.0.1
#配置自增主键从一开始
auto_increment_offset=1 
#配置自增主键每隔一进一，以防止主键自增的冲突
auto_increment_increment=2 
#要同步的数据库 默认同步所有库
#binlog-do-db=ftedu 
#不需要同步的数据库
#binlog-ignore-db=mysql
systemctl restart mysqld.service
#####################################################
#其他可选配置
#[可选] 0（默认）表示读写（主机），1表示只读（从机）
read-only=0
#设置日志文件保留的时长，单位是秒
binlog_expire_logs_seconds=6000
#控制单个二进制日志大小。此参数的最大和默认值是1GB
max_binlog_size=200M
#[可选]设置不要复制的数据库
binlog-ignore-db=test
#[可选]设置需要复制的数据库,默认全部记录。比如：binlog-do-db=atguigu_master_slave
binlog-do-db=需要复制的主数据库名字
#[可选]设置binlog格式
binlog_format=STATEMENT

创建新用户

在主库上创建一个新用户，用于数据同步,并授权

CREATE USER 'slave'@'%' IDENTIFIED BY 'Wang_jian123'; 
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
FLUSH PRIVILEGES;
#解释说明
%代表所有IP可以访问
grant replication slave on *.* to 'username'@'指定IP地址';
#注意：如果使用的是MySQL8，需要如下的方式建立账户，并授权slave：
CREATE USER 'slave1'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';
#此语句必须执行。否则见下面。
ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

从服务器配置

cp /etc/my.cnf /etc/my.cnf.bak
vim /etc/my.cnf
#服务id 需要和主库不一致
server-id=2
#打开MySQL中继日志 
relay-log-index=slave-relay-bin.index 
relay-log=slave-relay-bin
#打开从服务二进制日志 
log-bin=mysql-bin 
#使得更新的数据写进二进制日志中 
log-slave-updates=1
#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除
expire_logs_days=90
systemctl restart mysqld.service

配置主从

#登陆主库
[root@localhost ~]#mysql -uroot -pwangjian
#查询主库的同步状态
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      921 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#登录从库 设置主库的同步状态
[root@localhost ~]#mysql -uroot -pwangjian
#MASTER_LOG_FILE 和 MASTER_LOG_POS是主库状态的中的File和Position
CHANGE MASTER TO
MASTER_HOST='192.168.70.128',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='Wang_jian123',
MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=921;
#执行的过程开始
mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.70.128',
    -> MASTER_PORT=3306,
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='Wang_jian123',
    -> MASTER_LOG_FILE='master-bin.000001',
    -> MASTER_LOG_POS=921;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
#执行的过程结束
#参数解释说明
CHANGE MASTER TO
MASTER_HOST='主机的IP地址',
MASTER_USER='主机用户名',
MASTER_PASSWORD='主机用户名的密码',
MASTER_LOG_FILE='mysql-bin.具体数字',
MASTER_LOG_POS=具体值;
#开启主从slave 
start slave;
#关闭主从
stop slave;
#查看主从同步状态  G后面不要加分号
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.70.128
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 921
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes    #说明：Slave_IO_Running 和 Slave_SQL_Running 都为 Yes 则同步成功！
            Slave_SQL_Running: Yes    #说明：Slave_IO_Running 和 Slave_SQL_Running 都为 Yes 则同步成功！
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 921
              Relay_Log_Space: 528
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: cc49fe99-2ce6-11ee-98e1-000c29c65f97
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)
#若是有异常，或者报错可以
reset slave;清楚缓存
在A那边，执行：
flush logs;
show master status;
记下File, Position
在B端，执行，值根据实际情况修改
CHANGE MASTER TO MASTER_LOG_FILE='testdbbinlog.000008',MASTER_LOG_POS=107;
slave start;
show slave status \G

设置主主

生产建议用这个，就算一个关机了，另外一台也能读写，在启动后，数据也会同步过来。最大程度保持了数据的完整性。

#主1配置文件配置
cp /etc/my.cnf /etc/my.cnf.bak
vim /etc/my.cnf
#服务id 唯一
server-id=1 
#开启binlog ，日志名称
log_bin=master-log1  
log_bin-index=master-log1.index
#避免主键冲突，主键默认从1开始
auto-increment-offset=1
#配置自增主键每隔一进一，以防止主键自增的冲突
auto_increment_increment=2
#打开MySQL中继日志 
relay-log-index=slave-relay-bin.index 
relay-log=slave-relay-bin
#使得更新的数据写进二进制日志中 
log-slave-updates=1
#日志格式 
binlog_format=row
#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除
expire_logs_days=90
systemctl restart mysqld.service
#主2配置文件配置
#服务id 唯一
server-id=2
#开启binlog ，日志名称
log_bin=master-log2
log_bin-index=master-log2.index
#避免主键冲突，主键默认从2开始
auto-increment-offset=2
#配置自增主键每隔一进2，以防止主键自增的冲突
auto_increment_increment=2
#打开MySQL中继日志 
relay-log-index=slave-relay-bin.index 
relay-log=slave-relay-bin
#使得更新的数据写进二进制日志中 
log-slave-updates=1
#日志格式 
binlog_format=row
#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除
expire_logs_days=90
systemctl restart mysqld.service
#创建新用户用于同步数据使用
CREATE USER 'slave'@'%' IDENTIFIED BY 'Wang_jian123'; 
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
FLUSH PRIVILEGES;
#主主同步，值写对方的信息 相关信息查询 mysql> show master status;
#登录主2 设置主1的同步状态，在主1上进行信息查询
[root@localhost ~]#mysql -uroot -pwangjian
#MASTER_LOG_FILE 和 MASTER_LOG_POS是主库状态的中的File和Position
CHANGE MASTER TO
MASTER_HOST='192.168.70.128',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='Wang_jian123',
MASTER_LOG_FILE='master-log1.000001',
MASTER_LOG_POS=784;
#登录主1 设置主2的同步状态
#MASTER_LOG_FILE 和 MASTER_LOG_POS是主库状态的中的File和Position
CHANGE MASTER TO
MASTER_HOST='192.168.70.144',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='Wang_jian123',
MASTER_LOG_FILE='master-log2.000001',
MASTER_LOG_POS=784;
#开启主从slave 
start slave;
#查看主从同步状态  G后面不要加分号
mysql> show slave status \G

主从测试

#在主库创建一个数据库
create database demo;
#查看主库和从库是否都有这个库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
#可以发现同步过来了
#在主库上再创建一个表，并插入一条数据
use demo;
create table demo_table (id int not null);
insert into demo_table values (1);
查看从库是否同步成功
use demo;
mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| demo_table     |
+----------------+
1 row in set (0.00 sec)
mysql> select * from demo_table;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)
至此，mysql主从部署完成，以上配置的主从同步都是针对全库的

读写分离配置

在我们搭建的主从架构中，数据流向的单向的，只能由主库向从库同步，而从库的更新是无法同步到主库的。这便会造成一个问题：当不小心更新了从库，数据就会发现紊乱，最后导致数据不一致甚至同步发生错误导致中断。所以我们为了保证数据一致性需要在从库中加上以下配置：

vim /etc/my.cnf
#设置为只读 0（默认）表示读写（主机）
read_only=1
#重启服务
systemctl restart mysqld.service
注意：该配置只适用于普通用户，对于root等有super权限的用户无效！

GTID同步集群

以上搭建的集群方式，是基于Binlog日志记录点的方式来搭建的，这也是最为传统的MySQL集群搭建方式。而我们在show master status中可以看到有一个Executed_Grid_Set列是空的。实际上，这就是另外一种搭建主从同步的方式，即GTID搭建方式。这种模式是从MySQL5.6版本引入的。

GTID的本质也是基于Binlog来实现主从同步，只是他会基于一个全局的事务ID来标识同步进度。GTID即全局事务ID，全局唯一并且趋势递增，他可以保证为每一个在主节点上提交的事务在复制集群中可以生成一个唯一的ID 。

在基于GTID的复制中，首先从服务器会告诉主服务器已经在从服务器执行完了哪些事务的GTID值，然后主库会有把所有没有在从库上执行的事务，发送到从库上进行执行，并且使用GTID的复制可以保证同一个事务只在指定的从库上执行一次，这样可以避免由于偏移量的问题造成数据不一致。

他的搭建方式跟我们上面的主从架构整体搭建方式差不多。只是需要修改一些配置。

#修改主库配置
vim /etc/my.cnf
gtid_mode=on
enforce_gtid_consistency=on
#重启服务
systemctl restart mysqld.service

参考的文章:https://developer.aliyun.com/article/1165985

sql语句

别名as

常规设置

SELECT goods_name AS"商品名称",
market_price AS "市场价",
shop_price AS "本店价",
click_count 
FROM goods;

多个表查询出现相同的别名

对orderform订单信息表和user_address用户收货信息表，里面都有电话号码，需要用不同的别名查看

SELECT user_address.mobile AS "订单表中的电话号码",
users.mobile AS "用户收货信息表的电话号码",user_address.address
FROM user_address, users
WHERE user_address.user_id = users.user_id;

计算结果设置别名

SELECT goods_name AS 商品名称,market_price AS 市场价,cost_price AS 成本价,
(market_price - cost_price) AS 商品盈利 
FROM goods;

聚合函数设置别名

SELECT MAX(market_price) AS "市场价最高",MIN(market_price) AS "市场价最低" 
FROM goods; 

删除重复数据

distinct关键字 ，在一次查询中只能用一次，必须放在第一位

比如一个人购买多个商品，实际只有一个收获地址

未做删除的查询
SELECT consignee,address,mobile from orderform

做删除之后的查询

SELECT distinct consignee,address,mobile from orderform

限制查询结果

--只查询前5条数据
select * from orderform limit 5;

--查询从第三条数据开始算起的5条数据
select * from orderform limit 2,5;

--limit n offset m  ,n是代表行数  m是查询的起始位置
--从第四条数据开始的2条数据
select * from orderform limit 2 offset 3;

--在oracle中 查询前5条数据
select * from orderform where rownum <=5;

计算列查询

连接列值

在品牌表brand中，将查看品牌信息，将品牌名称列(name)和品牌分类列(cat_name)合并成一个新列，名为品牌信息，连接中+ oracle中使用 || 拼接

select name + cat_name AS '品牌信息' from brand;

查询中计算

+ – * / % 加减乘除求余

减法

--在goods商品信息表中，查询出每件商品的销售利润
SELECT goods_id AS 商品ID,goods_name AS 商品名称,
(shop_price - cost_price) AS 销售利润 
FROM goods;

乘法

SELECT goods_id AS 商品ID,goods_name AS 商品名称,
(shop_price * sales_sum) AS 销售额 
FROM goods;

综合运算

--销售数量*单件-销售数量—*进价
SELECT goods_id AS 商品ID,goods_name AS 商品名称,
(sales_sum*shop_price - cost_price*sales_sum)/sales_sum AS 销售利润 
FROM goods
WHERE sales_sum <> 0;

查询中的表达式

数值表达式

将商品进价增加50

SELECT goods_id AS 商品ID,goods_name AS 商品名称,cost_price AS 原进价,
cost_price + 50 AS 进价加
FROM goods;

字符表达式

--价格后面添加单位，并对两列重新命名
SELECT goods_id AS 商品ID,goods_name AS 商品名称,
convert(char(2),sales_sum)+'个' AS 销售数量,
convert(char(8),shop_price)+'元' AS 商场价格 FROM goods; 

--列无重命名
SELECT goods_id AS 商品ID,goods_name AS 商品名称,
sales_sum +'个' AS 销售数量,
shop_price+'元' AS 商场价格 FROM goods; 

表达式创建新列

--自动创建两个列
SELECT goods_id AS 商品ID,goods_name AS 商品名称,1+1,'字符'+'串列'
FROM goods;

条件查询

where

比较运算符

SELECT * FROM goods WHERE goods_id = 106;

— 使用>

SELECT goods_id,goods_name,click_count FROM goods 
WHERE click_count > 50;

<小于号

SELECT goods_id,goods_name,store_count FROM goods 
WHERE store_count < 1000;

>=

SELECT goods_id,goods_name,sales_sum 
FROM goods 
WHERE sales_sum >= 5;

<=

SELECT goods_id,goods_name,click_count
FROM goods 
WHERE click_count <= 20;

!> 不大于

SELECT goods_id,goods_name,shop_price
FROM goods 
WHERE shop_price !> 2000; 

!<不小于

SELECT goods_id,goods_name,shop_price
FROM goods 
WHERE shop_price !< 2000; 

!= 或者 <> 不等于

SELECT goods_id,goods_name,is_new FROM goods WHERE is_new != 0;
SELECT goods_id,goods_name,is_new FROM goods WHERE is_new <> 0;

范围查询

两个值之间的数据

SELECT goods_id AS 商品ID,goods_name AS 商品名称,market_price AS 市场价
FROM goods
WHERE market_price BETWEEN 1000 AND 3000; 

两个日期之间的数据

SELECT ISBN,bookname,INTime AS 数据录入时间 FROM bookinfo_zerobasis 
WHERE INTime
BETWEEN '2017-12-1' AND '2018-12-1';

不在两个数之间的

SELECT goods_id,goods_name,market_price 
FROM goods 
WHERE market_price NOT BETWEEN 2000 AND 3000; 

排序

并且使用order by关键字按照“商品编号”的降序排列该中的相关信息

SELECT * FROM goods ORDER BY goods_id DESC; 

逻辑运算符

and

SELECT goods_id,goods_name,shop_price 
FROM goods 
WHERE shop_price > 3000 AND shop_price < 6000;

SELECT goods_name,click_count,store_count,shop_price
FROM goods 
WHERE click_count > 20 AND store_count = 1000 AND shop_price > 2000;

or

SELECT ISBN,BookName,Writer,Price
FROM bookinfo_zerobasis
WHERE BookName = '零基础学Java' OR BookName = '零基础学PHP';

SELECT BookName,Price,pDate 
FROM bookinfo 
WHERE BookName LIKE '%PHP%' OR BookName LIKE '%Oracle%' OR BookName LIKE '%Android%';

not

SELECT goods_id,goods_name,store_count 
FROM goods 
WHERE NOT store_count = 1000;

SELECT goods_id,goods_name,store_count 
FROM goods 
WHERE store_count != 1000;

SELECT cat_id,goods_name,shop_price 
FROM goods 
WHERE cat_id = 191 OR cat_id = 123 AND shop_price > 2000;

SELECT cat_id,goods_name,shop_price 
FROM goods 
WHERE (cat_id = 191 OR cat_id = 123) AND shop_price > 2000;

格式化结果集

数值类型转换

小数转整数CAST

--sql server
SELECT TOP 6 user_id,email,CAST(total_amount AS int) AS total_amount 
FROM users;

去掉空格LTRIM

--sql server
SELECT TOP 6 address_id,LTRIM(consignee) AS consignee 
FROM user_address;

常见错误

ERROR 1067 (42000)

是因为sql_mode中的NO_ZEROR_DATE导制的，在strict mode中不允许’0000-00-00’作为合法日期

临时修改

使用下面的命令查看sql_mode

mysql>show variables like ‘sql_mode’;

±--------------±------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
±--------------±------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
±--------------±------------------------------------------------------------------------------------------------------------------------------------------+

将上面的NO_ZERO_DATE改为下面的 ALLOW_INVALID_DATES

mysql> set sql_mode=‘ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ALLOW_INVALID_DATES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;

上面的设置是临时设置，在重新登陆后，该设置又恢复为NO_ZERO_DATE

永久修改方式

修改my.cnf文件，在[mysqld]中添加

sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

重启mysql