瑞吉实战14-Mysql安装运维
瑞吉实战14–Mysql主从
实验内容
ab工具的安装和常规使用介绍
一、mysql单机安装
安装环境：centos版本（3.10.0-693.el7.x86_64），内核（CentOS Linux release 7.4.1708 (Core)）
cat /proc/version
cat /etc/redhat-release
uname -r
安装方法： mysql - CentOs7.3 安装 MySQL 5.7.19 二进制版本_个人文章 - SegmentFault 思否
# mysql依赖libaio
yum search libaio
yum install libaio
# 下载600M的安装包
cd ~
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
tar -zxvf ~/mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
ln -s /usr/local/mysql-5.7.19-linux-glibc2.12-x86_64/ /usr/local/mysql
# 新建用户组和用户
cd /usr/local/mysql/
groupadd mysql
useradd mysql -g mysql
# 创建目录并授权
mkdir data mysql-files
chmod 750 mysql-files
chown -R mysql .
chgrp -R mysql .
# 初始化MySQL,这步需要看一下初始密码，后面方便进行修改！
bin/mysqld --initialize --user=mysql # MySQL 5.7.6 and up
# 权限回收管控，授予mysql必要的读写权限
chown -R root .
chown -R mysql data mysql-files
# 添加到MySQL 启动脚本到系统服务
cp support-files/mysql.server /etc/init.d/mysql
cp support-files/mysql.server /etc/init.d/mysqld
# 给日志目录授予读写权限
mkdir /var/log/mariadb
touch /var/log/mariadb/mariadb.log
chown -R mysql:mysql /var/log/mariadb
# 修改配置，不然无法启动
sed -i "s#socket=/var/lib/mysql/mysql.sock#socket=/tmp/mysql.sock#g" /etc/my.cnf
# 启动mysql
service mysql.server start
# ======登录MySQL，并修改密码，授予远程访问权限=====
/usr/local/mysql/bin/mysql -uroot -p
ALTER USER 'root'@'localhost' identified by '123456';
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
flush privileges;
exit
二、数据库运维常用命令
# 添加删除自启动
chkconfig --add mysql # 删除把add换del即可
chkconfig --add mysqld
chkconfig --list
# 日常服务启停
service start mysql
service stop mysql
service status mysql
# 登录数据库
mysql [-h ip] [-P port] -u user -p[password]
# 创建读写用户(控制单个库！)
grant all privileges on test.* to 'hcbs'@'%' identified by '123456';
flush privileges;
# 创建只读用户
grant select ON test.* to `readuser`@`%` identified by'123456';
flush privileges;
# 查看用户及授予权限
select user,host from mysql.user;
SHOW GRANTS FOR 'readuset'@'%';
# 修改密码
ALTER USER 'masteruser'@'%' identified by '123456';
# 授予远程访问权限（同时创建账号、密码）
grant all privileges on test.* to 'masteruser'@'%' identified by '123456' with grant option;
flush privileges;
三、主从复制搭建手册
这里演示的是两台干净主机的初识配置，如果是主从不一致，需要重新同步具体看下一节
主节点配置脚本
# 第一步：修改Mysql数据库的配置文件/etc/my.cnf
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin #[必须]启用二进制日志
server-id=145 #[必须]服务器唯一ID
binlog_do_db=test #[可选]同步的数据库，支持多
#binlog_ignore_db 这项可选所以没有配置，意思 忽略复制哪些数据库
# 第二步：重启Mysql服务
service mysqld restart
# 第三步：登录Mysql数据库，执行下面SQL
GRANT REPLICATION SLAVE ON *.* to 'xiaoming'@'%' identified by '123456';
# 第四步：登录Mysql数据库，执行下面SQL，记录下结果中File和Position的值
show master status;
补充：如果担心主节点数据被修改可考虑锁表，待同步一致后再解锁。
# 锁表
flush tables with read lock;
# 解锁
unlock tables;
分支节点配置脚本
# 第一步：修改Mysql数据库的配置文件/etc/my.cnf
vim /etc/my.cnf
[mysqld]
server-id=146 #[必须]服务器唯一ID
# 第二步：重启Mysql服务
service mysqld restart
# 第三步：登录Mysql数据库，执行下面SQL， 尾部的logfile、position要根据实际填写！！！
change master to master_host='192.168.20.145',master_user='xiaoming',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=441;
start slave;
# 第四步：登录Mysql数据库，执行下面SQL，查看从数据库的状态
show slave status;
四、主从库日常运维
主库常用命令
# 查看主服务器的运行状态
mysql> show master status;
# 查看从服务器主机列表
mysql> show slave hosts;
# 获取binlog文件列表
mysql> show binary logs;
# 只查看第一个binlog文件的内容
mysql> show binlog events;
# 查看指定binlog文件的内容
mysql> show binlog events in 'mysql-bin.000001';
# 创建读写用户
grant all privileges on *.* to 'master'@'test' identified by '123456' with grant option;
flush privileges;
从库常用命令
# 启动从库复制线程
mysql> START SLAVE;
# 停止从库复制线程
mysql> STOP SLAVE;
# 查看从库状态
show slave status;
# 创建只读用户
grant select ON test.* to `readuser`@`%` identified by'123456';
从库不同步重新搭建
# 1、从库搭建过程略，可参看章节1，单机安装。
# 2、从库的slave配置可参看章节3
# 3、主库的同步账户应该是有效的
# 4、主库锁表、查询同步位、导出数据
/usr/local/mysql/bin/mysql -uroot -p
mysql> flush tables with read lock;
mysql> show MASTER status; # 记录主库的logfile、position
mysql> exit;
/usr/local/mysql/bin/mysqldump -uroot -p --databases db1 db2 > /tmp/test.sql
# 5、从库恢复数据库、与主库同步
mysql -uroot -p < /tmp/test.sql
mysql> change master to master_host='192.168.20.145',master_user='xiaoming',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=1433;
start slave;
mysql> show slave status;
# 4、主库解锁表
unlock tables;
# 5、检查
mysql> show slave hosts; # 在主库查看从库清单
mysql> show slave status; # 在从库查看主库连接信息
五、数据库备份还原
备份单个库或某几张表
# 导出1个或多个数据库
/usr/local/mysql/bin/mysqldump -uroot -p --databases db1 db2 > /tmp/test.sql
# 仅导出1张表
/usr/local/mysql/bin/mysqldump -uroot -p db1 table1 table2 > /tmp/test2.sql
恢复数据库
# 全部恢复，导入整个新库
mysql -uroot -p < /tmp/test.sql
# 部分恢复，创建一个库、导入指定库的若干张表
mysql> CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
mysql> use test;
mysql> source /tmp/test2.sql;
六、字符集查看修改
查看字符集（数据库、表、字段）
# 查看数据库编码：
SHOW CREATE DATABASE test;
# 查看表编码：
SHOW CREATE TABLE test.user;
# 查看字段编码：
SHOW FULL COLUMNS FROM test.user;
修改字符集
# 修改数据库编码及排序规则
ALTER DATABASE test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
# 修改表编码及排序规则
ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
九、资料备留
mysql生产服务器配置过程
# 显示设置密码
echo 'xxx' |passwd --stdin root;
mysql生产配置 （mysql version 5.7.28）
#
## my.cnf for MySQL 5.7/8.0
## autoor: yejr(yejinrong@zhishutang.com, http://imysql.com, QQ: 4700963)
##
## 叶金荣(yejr)
## 靠谱优质的在线培训品牌知数堂培训(http://zhishuedu.com)联合创始人
## 新浪微博: @叶金荣, 微信公众：老叶茶馆(imysql_wx)
## QQ群: 650149401
## 注意：个别建议可能需要根据实际情况作调整，请自行判断或联系我，本人不对这些建议结果负相应责任
## 本配置文件主要适用于MySQL 5.7/8.0版本
#
[client]
port = 3306
socket = /appdata/mysql-5.7.28/run/mysql.sock
[mysql]
prompt="\u@prd-bus-mysql-01 \R:\m:\s [\d]> "
no-auto-rehash
[mysqld]
user = mysql
port = 3306
basedir = /app/mysql-5.7.28
datadir = /appdata/mysql-5.7.28/data
socket = /appdata/mysql-5.7.28/run/mysql.sock
pid-file = /appdata/mysql-5.7.28/run/mysql.pid
character-set-server = utf8
skip_name_resolve = 1
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'
#若你的MySQL数据库主要运行在境外，请务必根据实际情况调整本参数
default_time_zone = "+8:00"
open_files_limit = 65535
back_log = 1024
max_connections = 3000
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 4500
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 96M
max_heap_table_size = 96M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /appdata/mysql-5.7.28/logs/slow.log
log-error = /appdata/mysql-5.7.28/logs/error.log
long_query_time = 2
#log_queries_not_using_indexes =1
#log_throttle_queries_not_using_indexes = 60
#min_examined_row_limit = 100
#log_slow_admin_statements = 1
#log_slow_slave_statements = 1
server-id = 3306
log-bin = /appdata/mysql-5.7.28/logs/binlog/mybinlog
sync_binlog = 0
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
#注意：MySQL 8.0开始，binlog_expire_logs_seconds选项也存在的话，会忽略expire_logs_days选项
expire_logs_days = 7
master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 2G
myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 4096M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 4G
innodb_undo_directory = /appdata/mysql-5.7.28/logs/undolog
innodb_undo_tablespaces = 95
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0
innodb_undo_log_truncate = 1
# some var for MySQL 5.7
innodb_checksums = 1
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
query_cache_size = 0
query_cache_type = 0
innodb_undo_logs = 128
#注意：MySQL 8.0.16开始删除该选项
internal_tmp_disk_storage_engine = InnoDB
innodb_status_file = 1
#注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 0
#performance_schema
performance_schema = 1
performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'
#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
[mysqldump]
quick
max_allowed_packet = 32M
mysql备份脚本
[root@prd-bus-mysql-02 yunuser]# cat /app/xtraBackup_database.sh
#!/bin/bash
/usr/bin/innobackupex --defaults-file=/appdata/mysql-5.7.28/conf/my.cnf --user=root --password='xxx' /appdata/xtrabackup
/bin/find /appdata/xtrabackup -ctime +2 -type d -exec rm -rf {} \;
crontab
[root@prd-bus-mysql-02 yunuser]# crontab -l
30 03 * * * /bin/sh /app/xtraBackup_database.sh >/dev/null 2>&1
*/5 * * * * ntpdate 192.168.70.151 > /dev/null 2>&1
评论系统未开启，无法评论！