QA
Q：修改配置/etc/my.cnf.d/mysql-server.cnf后重启失败[root@cent-8 /var/lib/mysql]$ systemctl status mysqld● mysqld.service - MySQL 8.0 database serverLoaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)Active: failed (Result: exit-code) since Wed 2021-11-03 20:27:11 CST; 32s agoProcess: 2008 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS)Process: 1659 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)Process: 2005 ExecStart=/usr/libexec/mysqld --basedir=/usr (code=exited, status=1/FAILURE)Process: 1969 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)Process: 1944 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)Main PID: 2005 (code=exited, status=1/FAILURE)Status: "Server startup in progress"Error: 13 (Permission denied)Nov 03 20:27:11 cent-8 systemd[1]: Starting MySQL 8.0 database server...Nov 03 20:27:11 cent-8 systemd[1]: mysqld.service: Main process exited, code=exited, status=1/FAILURENov 03 20:27:11 cent-8 systemd[1]: mysqld.service: Failed with result 'exit-code'.Nov 03 20:27:11 cent-8 systemd[1]: Failed to start MySQL 8.0 database server.A：配置文件中指定的目录并不存在！Q：mysql启动异常报错：①Process: 5461 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=1/FAILURE)②Process: 5597 ExecStart=/usr/libexec/mysqld --basedir=/usr (code=exited, status=1/FAILURE)A：①产生的原因可能是mysql异常停止，导致/var/lib/mysql/mysql.sock的文件没有及时清除。解决方案为：rm -rf /var/lib/mysql/mysql.sock 重启mysql即可；②根据日志信息知道了是 ‘ibdata1’ 数据文件必须是可写的，所以更改权限即可输入chmod -R 777 /var/lib/mysql
安装
[root@cent-8 ~]$ dnf install mysql-server[root@cent-8 ~]$ systemctl enable --now mysqld[root@cent-8 ~]$ mysql -V (#查看客户端版本)[root@cent-8 /var/log/mysql]$ mysql (#在这里可以看到服务端版本)grep password /var/log/mysql/mysqld.logmysqladmin -uroot password rootmysql -uroot -prootmysql> statusmysql> show databases;[root@cent-8 ~]$ mysql_secure_installation 初始化脚本提高安全性设置数据库管理员root口令禁止root远程登录删除anonymous用户帐号删除test数据库[root@centos8 ~]#export MYSQL_PS1="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_" (#临时修改mysql提示符)[root@centos8 ~]#mysql -uroot -pcentos --prompt="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_"[root@centos8 ~]#cat /etc/my.cnf.d/mysql-clients.cnf (#持久修改mysql提示符)[mysql]prompt="\\r:\\m:\\s(\\u@\\h) [\\d]>\\_"user=rootpassword=root(root@localhost) [(none)]> use mysql(root@localhost) [mysql]>
创建数据库（create database）
show databases;
create database db2；
show create database db2；
alter database db2 character set utf8;
drop database db2;
1.创建定义表（DDL）
直接创建
create table student；
(root@localhost) [mysql]> create table db1(id tinyint UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(20) NOT NULL,
-> age tinyint UNSIGNED ,
-> gender ENUM ("m","f") DEFAULT "M")
-> ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
show create table student;
desc db1;
show table status from db1\G
set @@auto_increment_offset=3;(初始值)
set @@auto_increment_increment=1;（步进值）
show variables like 'auto_inc%';
删表 drop table stu;
修改表名 alter table student rename students;
增加字段 alter table student add age tinyint after name;
删除字段 alter table student drop column age;
修改字段类型 alter table student modify age int;
修改字段名称 alter table students change column name mingzi char(8);
添加主键 alter table teacher add primary key (id);
删除主键 alter table teacher drop primary key;
通过查询现存表创建
create table stu select id,name,gender from student;
通过复制现存表结构创建
create table teacher like stu;
2.表数据操作（DML)
insert 插入数据
insert students (mingzi,age,gender) values ('zhao',23,'F');
update 修改数据
update students set age=21 **where** id < 3;（要有条件限制，否则将修改所有该字段内容！可利用—safe-updates避免）
delete 删除数据
delete from students **where** id=6;（要有条件限制，否则将清空所有内容！）
清空表保留表结构 truncate table students；
缩减表大小 optimize table students;
3.表数据查询（DQL）
select ***** from students;
字段和表可以使用别名：select mingzi name,age nianling from students;
用where语句过滤选择 select * from students WHERE id <=4; 比较
select * from students WHERE id between 1 and 5; 范例
select * from students WHERE id in (1,3,5); 不连续
select * from students WHERE age IS NULL; 空查询
select * from students WHERE mingzi LIKE 'z%'; 模糊查询
对结果排序 select * from students where id IS NOT NULL order by age DESC; 倒序
select * from students where id IS NOT NULL order by age ASC; 正序
对结果输出行数设限 select * from students limit 1,3; 第1行开始，显示3行
去除重复行 select distinct mingzi from students;
对结果分组 select gender,count(*) from students where id >1 group by gender;
select gender,count(*) from students where id >1 group by gender with rollup; 对分组结果汇总
select gender,count(*) from students where id >1 group by gender having count(*) >1;
子查询
select name,age from student **where** age **>** (select avg(age) from teacher);select name,age from student **where** age **in** (select age from teacher);
联合查询（纵向合并）
select name,age,gender from teacher **union** select name,age,gender from student;
交叉连接（笛卡尔乘积）
select * from student **cross join** teacher;select * from student**,**teacher;
select * from student cross join teacher;
+----+-------+------+--------+----+-------+------+--------+
| id | name | age | gender | id | name | age | gender |
+----+-------+------+--------+----+-------+------+--------+
| 1 | zyy | 21 | M | 4 | baoyu | 25 | M |
| 1 | zyy | 21 | M | 2 | daiyu | 23 | M |
| 1 | zyy | 21 | M | 1 | hanlu | 25 | M |
| 3 | yuany | NULL | M | 4 | baoyu | 25 | M |
| 3 | yuany | NULL | M | 2 | daiyu | 23 | M |
| 3 | yuany | NULL | M | 1 | hanlu | 25 | M |
select student.name sname,teacher.name tname from student cross join teacher;
+-------+-------+
| sname | tname |
+-------+-------+
| zyy | baoyu |
| zyy | daiyu |
| zyy | hanlu |
| yuany | baoyu |
| yuany | daiyu |
| yuany | hanlu |
内连接★（共同部分）
①select * from student **inner join** teacher **on** student**.**age =teacher**.**age;
→select * from **student s** inner join **teacher t** on **s.age =t.age**;
→select * from student s inner join teacher t on s.age =t.age where s.gender='F';
②select * from student**,**teacher **where** student.age =teacher.age;
→select * from student**,**teacher **where** student.age =teacher.age **and** s.gender<>t.gender;
左/右外连接
select * from student s **left outer join** teacher t **on** s.age = t.age;select * from student s **left outer join** teacher t **on** s.age = t.age **where** s.id>1;select * from student s **left outer join** teacher t **on** s.age = t.age **and** s.id>1;
select * from student s **right outer join** teacher t **on** s.age = t.age;
完全外连接
MySQL不支持，但可以利用左/右外连接和联合查询实现select * from student s left outer join teacher t on s.age=t.age **union**select * from student s right outer join teacher t on s.age=t.age;
自连接—别名
select m.name,n.name from **student m** inner join **student n** on m.id=n.tid;select name,course,score from students st **inner join** scores sc on st.stuid=sc.stuid **inner join** courses co on sc.courseid=co.CourseID;三表连接
03:19:11(root@localhost) [db1]> select * from student;
+----+-------+------+--------+------+
| id | name | age | gender | tid |
+----+-------+------+--------+------+
| 1 | zyy | 21 | M | NULL |
| 3 | yuany | NULL | M | 1 |
| 5 | zhao | 23 | F | 7 |
| 7 | sha | 30 | M | 5 |
| 8 | zhao | 30 | M | 7 |
+----+-------+------+--------+------+
5 rows in set (0.00 sec)
03:19:13(root@localhost) [db1]> select m.name,n.name from student m inner join student n on m.id=n.tid;
+------+-------+
| name | name |
+------+-------+
| zyy | yuany |
| sha | zhao |
| zhao | sha |
| sha | zhao |
+------+-------+
4 rows in set (0.00 sec)
4.视图 view
保存实表的查询结果，相当于别名create view s_t as select m.name s_name,n.name t_name from student m inner join student n on m.id=n.tid;how create view s_t;→show create table s_t;
5.函数、变量
函数：分为系统内置函数和自定义函数UDF（ 保存在mysql.proc表 ）create function hello() returns varchar(20) return "hello word";select hello(); 调用函数show function status;show create function hello;drop function hello;
set global log_bin_trust_function_creators=ON;
create function hello() returns varchar(20) return "hello word";
(root@localhost) [db1]> select hello();
+------------+
| hello() |
+------------+
| hello word |
+------------+
1 row in set (0.01 sec)
系统变量：MySQL数据库中内置的变量，可用@@var_name引用
普通变量：在当前会话中有效，可用@var_name引用
局部变量：在函数或存储过程内才有效，需要用delare声明，之后直接用var_name引用set parameter_name = valueselect into parameter_name
MariaDB [hellodb]> select count(*) from students into @num;
MariaDB [hellodb]> select count(*) into @num from students;
MariaDB [hellodb]> select @num;
6.存储过程 procedure
存储过程把经常使用的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调用,省去了编译的过程，提高了运行速度，同时降低网络数据传输量(存储过程保存在mysql.proc表中)
7.触发器 trigger
create trigger tri_ins after insert on student for each rowupdate total_stu set total_atudent=total_atudent-1;
6.事件 event
create event test on schedule every 1 second do insert into event_list values(do_insert_time,now());
select @@event_scheduler; 默认值为off
set global event_scheduler=1; 临时开启事件调度功能
show processlist;
create table events_list (event_test varchar(20) not null,event_start timestamp not null);
create event test on schedule every 1 second do insert into events_list values('test_start',now());
select * from event_list;
alter event event_every_second disable;
show events\G
drop event test;
8.用户管理
create user 'zhuyuany'@'10.0.0.%' identified by 'zhuyuany';mysql -uzhuyuany -pzhuyuany -h10.0.0.18
zhuyuanyuan520hxhyn520
RENAME USER old_user_name TO new_user_name;
DROP USER 'USERNAME'@'HOST;
ALTER USER test@'%' IDENTIFIED BY 'centos'; 通用改密码方法, 用户可以也可通过此方式修改自已的密码,MySQL8 版本修改密码
9.权限
grant all on *.* to 'zhuyuany'@'10.0.0.%';
REVOKE DELETE ON testdb.* FROM 'testuser'@172.16.0.%’;
Help SHOW GRANTS
SHOW GRANTS FOR 'user'@'host';
SHOW GRANTS FOR CURRENT_USER[()];
架构及性能优化
服务器-选项
获取当前启动选项 mysqld --print-defaults
mysqld --verbose --help
# 在命令行设置
/usr/bin/mysqld_safe --skip-name-resolve=1
/usr/libexec/mysqld --basedir=/usr
# 在配置文件中设置
vim /etc/my.cnf
[mysqld]
skip_name_resolve=1
skip-grant-tables
服务器-系统变量
show global variables; 全局 → set global character_set_results=utf8mb4；
show session variables; 会话 set @@global.character_set_results=utf8mb4;
#有的系统变量不是服务器选项,写入配置文件将导致无法启动！
show variables like 'max_co%';
select @@max_connections;
服务器-状态变量
show global status; 全局
show session status; 会话
show status like "innodb_page_size";
select @@max_connections;
服务器变量SQL_MODE
show variables like 'sql_mode';
set sql_mode="ONLY_FULL_GROUP_BY";
B+Tree索引
show index from student;
create index indexname on student(name);
explain select * from student where name like 'daiyu';
#创建复合索引--跳过查询复合索引的前面字段,后续字段的条件查询无法利用复合索引
create index idx_name_age on students(name,age);
profile工具
#打开后，会显示语句执行详细的过程 set profiling=ON;
select @@profiling;
#查看语句结果中的query_id值 show profiles;
#显示语句详细执行步骤及时长 show profile for query 2;
#显示cpu使用情况 show profile cpu for query 2;
锁机制
lock tables students read ;
unlock tables ;
事务
ACID特性： atomicity原子性 consistency一致性 Isolation隔离性 durability持久性
begin;
insert student (name,age) values('xuepan',18);
rollback;
begin;
insert student(name,age) values('jialian',28);
commit；
死锁：两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态
#查看正在进行的事务 show processlist;
#杀掉正在进行的事务 kill 16
#查看事务锁的超时时长 show global variables like 'innodb_lock_wait_timeout';
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
SERIALIZABLE
#MySQL8.0之前 set tx_isolation='READ-UNCOMMITTED' #MySQL8.0 set transaction_isolation='READ-UNCOMMITTED'日志管理
事务日志：transaction log 事务日志的写入类型为“追加”，因此其操作为“顺序IO”；
通常也被称为：预写式日志 write ahead logging
事务日志文件： ib_logfile0， ib_logfile1
- 错误日志 error log
- 通用日志 general log
- 慢查询日志 slow query log
- 二进制日志 binary log
- 中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件
数据备份
一、主从复制
```bash yum install -y mariadb-server;systemctl enable —now mariadb;systemctl status mariadb mysqladmin -uroot password root
[root@rs1 /etc/my.cnf.d]$ vim mysqld.cnf [mysqld] server-id=8 log-bin MariaDB [(none)]> show master status; +——————————+—————+———————+—————————+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +——————————+—————+———————+—————————+ | mariadb-bin.000001 | 245 | | | +——————————+—————+———————+—————————+ MariaDB [(none)]> grant replication slave on . to repluser@’10.0.0.%’ identified by ‘repluser’;
[root@rs2 /etc/my.cnf.d]$ vim mysqld.cnf [mysqld] log_bin server-id=17
MariaDB [mysql]> help change master to;
MariaDB [mysql]> CHANGE MASTER TO MASTER_HOST=’10.0.0.7’,
-> MASTER_USER=’repluser’,
-> MASTER_PASSWORD=’repluser’,
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE=’mariadb-bin.000001’,
-> MASTER_LOG_POS=245;
MariaDB [mysql]> start slave;
MariaDB [mysql]> show slave status\G
主服务器非新建时,主服务器运行一段时间后，新增从节点服务器 mysqldump -uroot -proot -A -F —single-transaction —master-data=1 >/root/fullbackup`date +%F%T`.sql
MariaDB [(none)]> set global innodb_flush_log_at_trx_commit=2; #建议优化主和从节点服务器的性能 MariaDB [(none)]> set global sync_binlog=0; MariaDB [(none)]> show variables like ‘sync_binlog’;
新增节点 yum install -y mariadb-server;systemctl enable —now mariadb;systemctl status mariadb mysqladmin -uroot password root vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=27 read-only
grep ‘^CHANGE MASTER’ fullbackup_2021-12-04_18\:45\:35.sql
CHANGE MASTER TO MASTER_LOG_FILE=’mariadb-bin.000002’, MASTER_LOG_POS=245;
vim fullbackup_2021-12-04_18\:45\:35.sql
CHANGE MASTER TO MASTER_HOST=’10.0.0.7’,
MASTER_USER=’repluser’,
MASTER_PASSWORD=’repluser’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’mariadb-bin.000002’,
MASTER_LOG_POS=245;
show slave status\G;
<a name="pyHW8"></a>
## 级联复制
<a name="xG1yk"></a>
## 主主复制
容易产生的问题：数据不一致；因此慎用
<a name="rsbYd"></a>
## 半同步复制
<a name="Nd3uo"></a>
# 二、中间件代理服务器MyCat (读写分离)
<br /> 1、创建 MySQL 主从数据库 <br /> 2、在10.0.0.8安装mycat并启动 <br /> 3、在mycat 服务器上修改server.xml文件配置Mycat的连接信息 <br /> 4、修改schema.xml实现读写分离策略 <br /> 5、在后端主服务器创建用户并对mycat授权 <br /> 6、在Mycat服务器上连接并测试 <br /> 7、通过通用日志确认实现读写分离 <br /> 8、停止从节点，MyCAT自动调度读请求至主节点 <br /> 9、MyCAT对后端服务器的健康性检查方法 select user()
<a name="VQc6s"></a>
# 三、MySQL高可用MHA,PXC
<a name="HCEsG"></a>
## MHA
<br />1. MHA利用 SELECT 1 As Value 指令判断master服务器的健康性,一旦宕机,从宕机的master保存二进制日志事件（binlog events）<br />2. 识别含有最新更新的slave<br />3. 应用差异的中继日志（relay log）到其他的slave<br />4. 应用从master保存的二进制日志事件（binlog events）<br />5. 提升一个slave为新的master<br />6. 使其他的slave连接新的master进行复制
```bash
1. 在管理节点上安装两个包mha4mysql-manager和mha4mysql-node (不支持CentOS8，只支持CentOS7 以下版本)
2. 在所有MySQL服务器上安装（支持CentOS 8，7，6）
3. 在所有节点实现相互之间ssh key验证
4. 在管理节点建立配置文件--/etc/mastermha/app1.cnf
5. 实现Master
6. 实现 slave
7. 检查Mha的环境--masterha_check_ssh
8. 启动MHA--nohup masterha_manager
9. 排错日志--/data/mastermha/app1/manager.log
10 故障，当 master down机后,mha自动退出
11. 如果再次运行MHA,需要先删除下面文件--/data/mastermha/app1/
Galera Cluster——-PXC
1 环境准备
pxc1:10.0.0.7
pxc2:10.0.0.17
pxc3:10.0.0.27
pxc4:10.0.0.37
2 在三个节点都安装好PXC 5.7
3 在各个节点上分别配置mysql及集群配置文件
4 启动PXC集群中第一个节点
5 启动PXC集群中其它所有节点
6 查看集群状态，验证集群是否成功
7 在PXC集群中加入节点
8 在PXC集群中修复故障节点
Galera Cluster——- MariaDB
在三个节点上都实现并配置dnf install mariadb-server-galera -y
启动第一节点
再启动其它节点
若有收获，就点个赞吧
0 人点赞
