3.Mysql单机+主从 一篇就够
下载地址<u>https://dev.mysql.com/downloads/mysql/</u>
图片5.png
卸载系统自带的Mariadb
[root@bici-test2 lib]# rpm -qa|grep mariadb
mariadb-libs-5.5.64-1.el7.x86_64
[root@bici-test2 lib]# rpm -e --nodeps mariadb-libs-5.5.64-1.el7.x86_64
安装必要软件yum install -y libaio
检查mysql是否存在
rpm -qa | grep mysql
检查mysql用户是否存在
cat /etc/group | grep mysql
cat /etc/passwd | grep mysql
创建MySQL用户组
groupadd mysql
添加mysql用户
useradd -g mysql mysql
修改密码 starquest
passwd mysql
重复输入两次密码后会提示passwd: all authentication tokens updated successfully.
将下载包mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz上传至linux服务器/home/software/下
解压
tar zxvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
重命名
[root@bici-test2 software]# mv mysql-5.7.27-linux-glibc2.12-x86_64/ mysql57
更改所属组和用户
[root@bici-test2 software]# chown -R mysql mysql57
[root@bici-test2 software]# chgrp -R mysql mysql57
[有的解压出来已有data文件]新建mysqldata文件夹
[root@bici-test2 software]# cd mysql57
[root@bici-test2 mysql57]# mkdir data
[root@bici-test2 mysql57]# chown -R mysql:mysql data
删除原有my.cnf
rm /etc/my.cnf
配置my.cnf
vi /etc/my.cnf
添加以下内容
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
skip-name-resolve
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=/home/software/mysql57
# 设置mysql数据库的数据的存放目录
datadir=/home/software/mysql57/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
保存并退出
初始化安装
[root@bici-test2 mysql57]# bin/mysqld --initialize --user=mysql --basedir=/home/software/mysql57 --datadir=/home/software/mysql57/data/
图片6.png
注意保存方框内的初始密码 N)efIQnb/5Ee
配置mysql
[root@bici-test2 mysql57]# cp support-files/mysql.server /etc/init.d/mysqld [root@bici-test2 mysql57]# chown 777 /etc/my.cnf
[root@bici-test2 mysql57]# chmod +x /etc/init.d/mysqld
启动mysql
[root@bici-test2 mysql57]# /etc/init.d/mysqld start
Starting MySQL.
图片7.png
设置开机启动mysql
[root@bici-test2 mysql57]# chkconfig --level 35 mysqld on
[root@bici-test2 mysql57]# chkconfig --list mysqld
[root@bici-test2 mysql57]# chmod +x /etc/rc.d/init.d/mysqld
[root@bici-test2 mysql57]# chkconfig --add mysqld
[root@bici-test2 mysql57]# chkconfig --list mysqld
配置/etc/profile
vi /etc/profile
添加
export PATH=$PATH:/home/software/mysql57/bin
使之生效
source /etc/profile
登录mysql，密码为刚刚保存的初始密码
mysql -uroot -p
图片8.png
修改密码为starquest
mysql> set PASSWORD = PASSWORD('starquest');
mysql> flush privileges;
允许远程访问
mysql> use mysql
mysql> update user set host='%' where user='root';
图片9.png
%表示任何主机都可以通过root登录
退出mysql
mysql> exit
重启mysql
[root@bici-test2 mysql57]# /etc/init.d/mysqld restart
用navicat连接下
图片10.png
3.1主从同步
1.环境 宿主机 CentOS Linux release 7.9.2009 (Core)
mysql:5.7 mysql1(master): x.x.x.2:3306
mysql2(slave): x.x.x.3:3306
2.配置
mysql1(master): x.x.x.2 /etc/my.cnf 配置文件设置
mysql master1 config
[mysqld]
server-id = 1 # 节点ID，确保唯一
log config
log-bin = mysql-bin #开启mysql的binlog日志功能
sync_binlog = 1 #控制数据库的binlog刷到磁盘上去 , 0 不控制，性能最好，1每次事物提交都会刷到日志文件中，性能最差，最安全
binlog_format = mixed #binlog日志格式，mysql默认采用statement，建议使用mixed
expire_logs_days = 7 #binlog过期清理时间
max_binlog_size = 100m #binlog每个日志文件大小
binlog_cache_size = 4m #binlog缓存大小
max_binlog_cache_size= 512m #最大binlog缓存大
binlog-ignore-db=mysql #不生成日志文件的数据库，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行
auto-increment-offset = 1 # 自增值的偏移量
auto-increment-increment = 1 # 自增值的自增量
slave-skip-errors = all #跳过从库错误
mysql1(slave): x.x.x.3 /etc/my.cnf 配置文件设置
[mysqld]
server-id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%
重启两个mysql，让配置生效
- master数据库，创建复制用户并授权
进入master的数据库，为master创建复制用户
CREATE USER repl_user IDENTIFIED BY 'repl_passwd';
赋予该用户复制的权利
grant replication slave on .to 'repl_user'@'x.x.x.2'identified by 'repl_passwd';
FLUSH PRIVILEGES;
查看master的状态
show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000005 120| | mysql | |+------------------+----------+--------------+------------------+-------------------+1row inset (0.00 sec)
配置从库
mysql> CHANGE MASTER TO
MASTER_HOST = 'x.x.x.2',
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'repl_passwd',
MASTER_PORT = 3307,
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=120,
MASTER_RETRY_COUNT = 60,
MASTER_HEARTBEAT_PERIOD = 10000;
MASTER_LOG_FILE='mysql-bin.000005',#与主库File 保持一致
MASTER_LOG_POS=120 , #与主库Position 保持一致
- 启动从库slave进程
mysql> start slave;
Query OK,0 rows affected (0.04 sec)
3.2 验证
操作主库 看从库是否同步
3.3 慢sql + mysqldumpslow
show variables like '%slow_query_log%';
showvariables like '%long_query_time%' ;
show variables like "general_log%";
show variables like 'log_queries_not_using_indexes';
set GLOBAL slow_query_log_file = '/home/mysql/slow_sql.log';
set GLOBAL general_log_file = '/home/mysql/bogon.log';
set GLOBAL general_log = 'ON';
set global log_queries_not_using_indexes = 'ON';
set GLOBAL log_output = 'table,FILE';
set long_query_time = 1;
set GLOBAL slow_query_log = 'ON';
分析日志
mysqldumpslow -s t -t 10 bogon-slow.log
3.4 创建低权限账号
admin 增删改记录 无drop操作
user 只读
GRANT SELECT ON . TO 'user'@'%' IDENTIFIED BY 'starquest';
GRANT SELECT,UPDATE,INSERT,DELETE ON . TO 'admin'@'%' IDENTIFIED BY 'starquest';
3.5****慢sql
TRUNCATE TABLE mysql.slow_log;
SELECT COUNT(0) from mysql.slow_log;
show variables like 'slow_query%';
show variables like 'long_query_time';
set GLOBAL slow_query_log = 'ON';
set GLOBAL long_query_time = 2;
SHOW VARIABLES LIKE'log_output';
SET GLOBAL log_output='TABLE';
SELECT CAST(sql_text AS char) FROM mysql.slow_log
SELECT CAST(sql_text AS char) sqltext , a.* FROM mysql.slow_log a GROUP BY a.sql_text order by a.query_time desc limit 100 ;
SELECT CAST(sql_text AS char) sqltext , a.* FROM mysql.slow_log a order by a.query_time desc limit 50;
SELECT * FROM mysql.slow_log a order by a.query_time desc limit 10 ;
SELECT * FROM mysql.slow_log a limit 10 ;
mysql 参数调优
show status WHERE Value != 0 and Variable_name like '%Max_used_connections%';
show variables like '%max_connections%';
set global max_connections = 500;
show variables like "%expire_logs%";
set global expire_logs_days = 1;
flush logs;
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;
ALTER TABLE craft_process_step_param ADD need_alert tinyint(1) NOT NULL DEFAULT 0 COMMENT '值异常时是否需要报警 0:不报警 1:报警';
