mysql数据库基础知识总结 概述在centos系统下的默认安装目录结构目录说明/usr/bin客户端程序和脚本/usr/sbinmysqld服务器/var/lib/mysql日志文件，数据库/usr/share/doc/packages文档/usr/include/mysql包含（头）文件/usr/lib/mysql数据/usr/share/mysql错误消息和字符集文件/usr/share/sql-bench基准程序主从复制{callout color="#f0ad4e"}mysql复制概述 mysql数据库支持单向、异步复制，一个服务器充当主服务器。一个或多个充当从服务器 mysql数据库复制基于主服务器在二进制日志中跟踪所有数据的更新，进行复制，在主服务器上启用二进制日志 从服务器执行不会干扰主服务器，在备份过程中主服务器可以继续处理更新。复制实现细节 mysql使用3个线程来执行复制功能（其中1个在主服务器上，另两个在从服务器上）。 主服务器Binlog Dump 线程 从服务器I/O线程 从服务器SQL线程mysql数据库多主一从实现方式也是基于一主一从的实现{/callout}常用语句# 查询一个数据库有多少张数据表； select count(*) TABLES,table_schema from information_schema.TABLES where table_schema = 'ultrax' group by table_schema; # 查看某张表的字段信息； mysql> desc pre_common_connect_guest; # 查看mysql默认引擎 show variables like '%storage_engine%';锁表问题# mysql查看表的状态 show OPEN TABLES where In_use > 0; # 查看锁表进程 mysql> show processlist; mysql> kill 112498; #进程ID重置密码# 无密码启动 /usr/local/mysql/bin/mysqld_safe --skip-grant-tables >/dev/null 2>&1 &update user set password = Password('123456') where User = 'root'; flush privileges;

mysql数据库基本操作语句,模拟误删数据恢复 {card-default label="mysql" width="80%"}{/card-default}前言{callout color="#f0ad4e"} 作为小站长，mysql数据库算是比较常用的了。作为运维，肯定遇到过数据被误删的情况。下面模拟数据库为误操作删除后的恢复过程。{/callout}mysql常用配置开启binlog修改配置vim /etc/my.cnf.d/mariadb-server.cnf server-id = 1 log-bin=mysql-bin binlog_format=mixed expire_logs_days = 10常用查询-- 查看binlog 状态 use mysql show variables like '%log_bin%'; -- 查看所有binlog文件列表 show master logs; -- 查看最后一个binlog日志的编号名称及其最后一个操作事件pos结束点的值 show master status; -- 刷新日志，此刻开始产生一个新编号的binlog文件 Flush logs; -- 每当mysqld服务重启时，会自动执行刷新binlog日志命令，mysqldump备份数据时加-F选项也会刷新binlog日志 -- 清空所有binlog reset master; -- 查看binlog 内容 -- 方法1 mysqlbinlog mysql-bin.000001 -- 方法2 show binlog events in 'mysql-bin.000001'; -- 指定查询 -- 从mysql-bin.000003 的pos 点328 开始 show binlog events in 'mysql-bin.000003' from 328; -- 从pos点154开始查询，中间跳过1行，查询2条数据 show binlog events in 'mysql-bin.000004' from 256 limit 1,2; 数据备份恢复#数据备份 mysqldump --single-transaction -uroot -proot@123 -S /opt/app/mysql/mysql.sock taskmonitor | /bin/gzip >/tmp/mysql_taskmonitor_`date +%m%d`.gz #下载 scp root@ip:/tmp/mysql_taskmonitor_0913.gz . #导入 gzip -d mysql_taskmonitor_0913.gz mysql -uroot -p123456 taskmonitor < mysql_taskmonitor_0913 建库授权CREATE DATABASE `taskmonitor` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; create user 'tmuser'@'%' IDENTIFIED BY 'tmuser@123'; grant all privileges on taskmonitor.* to 'tmuser'@'%'; flush privileges;清理数据库删除所有表SELECT CONCAT('drop table ',table_name,';') FROM information_schema.`TABLES` WHERE table_schema='wpc';模拟恢复实验配置备份计划任务检查binlog 是否开启use mysql show variables like '%log_bin%';备份脚本隐藏内容，请前往内页查看详情定时全量备份，每周日4点10分 全备10 4 * * 0 /bin/bash /opt/scripts/backup_db_taskmonitor.sh >/dev/null 2>&1手动执行/bin/bash /opt/scripts/backup_db_taskmonitor.sh执行误删操作drop database taskmonitor;恢复准备数据恢复，登录数据库，刷新binlogflush logs;备份 mysql-bin 文件mkdir dbbak && cp /data/mysql/mysql-bin.* dbbak/根据binlog继续恢复，找出错误操作的posmysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000002 |grep -C 10 -i "drop database"{card-default label="pos" width="75%"}{/card-default}{message type="success" content="通过上图看到误操作的点是604"/}查找mysqldump全备结束的pos点grep -i "change master" /data/db_backup/2021-09-29-15-29-13.taskmonitor{card-default label="查询结果" width="75%"}{/card-default}{message type="success" content="全备pos点 371"/}开始恢复通过全备份恢复数据mysql -uroot -p123456 < /data/db_backup/2021-09-29-15-29-13.taskmonitor开始增量恢复到误操作,同一个binlog内mysqlbinlog --start-position=371 --stop-position=604 mysql-bin.000002 |mysql -uroot -p123456

centos7安装配置wordpress博客系统 {card-default label="wordpress" width="75%"}{/card-default}前言{card-default label="LAMP架构的介绍" width="100"}LAMP是 linux Apache MySQL PHP的简写，即把Apache MySQL PHP 安装在linux系统上，组成一个环境来运行PHP网站。这里的Apache是httpd服务。这些可以安装在一个机器上，也可以安装在多台机器上，但是httpd和PHP安装在一台机器上（php作为httpd的一个模块存在的。 他们两者必须要在一起，才能实现效果）{/card-default}安装配置apache+php服务{card-default label="视频1" width="80%"}{bilibili bvid="BV1jA411u75e" page="wordpress博客配置"/}{/card-default}apache和php安装命令yum install httpd httpd-devel yum -y install epel-release yum -y install https://rpms.remirepo.net/enterprise/remi-release-7.rpm yum -y install yum-utils yum-config-manager --enable remi-php74 yum -y install php php-cli php-fpm php-mysqlnd php-zip php-devel php-gd php-mcrypt php-mbstring php-curl php-xml php-pear php-bcmath php-json php-redisphp配置优化{card-describe title="php-fpm.conf"}[global] pid = /var/run/php-fpm/php-fpm.pid error_log = /opt/servicelogs/php-fpm/error.log daemonize = yes include=/etc/php-fpm.d/*.conf{/card-describe}{card-describe title="php-fpm.d/www.conf"}[www] listen = /var/run/php-fpm/php-fpm.sock ;listen = 127.0.0.1:9000 listen.allowed_clients = 127.0.0.1 listen.owner = nginx listen.group = nginx listen.mode = 0660 listen.backlog = 16384 user = nginx group = nginx pm = static pm.max_children = 20 pm.start_servers = 10 pm.min_spare_servers = 10 pm.max_spare_servers = 35 pm.max_requests = 10000 request_terminate_timeout = 180 request_slowlog_timeout = 30 slowlog = /opt/servicelogs/php-fpm/www-slow.log ;access.log = /opt/servicelogs/php-fpm/access.$pool.log ;access.format = "%R - %u %t \"%m %r%Q%q\" %s %f %{seconds}d %{megabytes}M %{user}C%%" ping.path = /php5fpm-ping pm.status_path = /php5fpm-status{/card-describe}{card-describe title="php.ini"}[PHP] engine = On short_open_tag = Off precision = 14 output_buffering = 4096 zlib.output_compression = Off implicit_flush = Off unserialize_callback_func = serialize_precision = 17 disable_functions = disable_classes = zend.enable_gc = On expose_php = On max_execution_time = 30 max_input_time = 60 memory_limit = 128M error_reporting = E_ALL & ~E_DEPRECATED & ~E_STRICT display_errors = Off display_startup_errors = Off log_errors = On log_errors_max_len = 1024 ignore_repeated_errors = Off ignore_repeated_source = Off report_memleaks = On track_errors = Off html_errors = On variables_order = "GPCS" request_order = "GP" register_argc_argv = Off auto_globals_jit = On post_max_size = 256M auto_prepend_file = auto_append_file = default_mimetype = "text/html" default_charset = "UTF-8" doc_root = user_dir = enable_dl = Off file_uploads = On upload_max_filesize = 2M max_file_uploads = 20 allow_url_fopen = On allow_url_include = Off default_socket_timeout = 60 [CLI Server] cli_server.color = On [Date] [filter] [iconv] [intl] [sqlite] [sqlite3] [Pcre] pcre.jit=0 [Pdo] [Pdo_mysql] pdo_mysql.cache_size = 2000 pdo_mysql.default_socket= [Phar] [mail function] sendmail_path = /usr/sbin/sendmail -t -i mail.add_x_header = On [SQL] sql.safe_mode = Off [ODBC] odbc.allow_persistent = On odbc.check_persistent = On odbc.max_persistent = -1 odbc.max_links = -1 odbc.defaultlrl = 4096 odbc.defaultbinmode = 1 [Interbase] ibase.allow_persistent = 1 ibase.max_persistent = -1 ibase.max_links = -1 ibase.timestampformat = "%Y-%m-%d %H:%M:%S" ibase.dateformat = "%Y-%m-%d" ibase.timeformat = "%H:%M:%S" [MySQLi] mysqli.max_persistent = -1 mysqli.allow_persistent = On mysqli.max_links = -1 mysqli.cache_size = 2000 mysqli.default_port = 3306 mysqli.default_socket = mysqli.default_host = mysqli.default_user = mysqli.default_pw = mysqli.reconnect = Off [mysqlnd] mysqlnd.collect_statistics = On mysqlnd.collect_memory_statistics = Off [PostgreSQL] pgsql.allow_persistent = On pgsql.auto_reset_persistent = Off pgsql.max_persistent = -1 pgsql.max_links = -1 pgsql.ignore_notice = 0 pgsql.log_notice = 0 [bcmath] bcmath.scale = 0 [browscap] [Session] session.save_handler = files session.use_strict_mode = 0 session.use_cookies = 1 session.use_only_cookies = 1 session.name = PHPSESSID session.auto_start = 0 session.cookie_lifetime = 0 session.cookie_path = / session.cookie_domain = session.cookie_httponly = session.serialize_handler = php session.gc_probability = 1 session.gc_divisor = 1000 session.gc_maxlifetime = 1440 session.referer_check = session.cache_limiter = nocache session.cache_expire = 180 session.use_trans_sid = 0 session.hash_function = 0 session.hash_bits_per_character = 5 url_rewriter.tags = "a=href,area=href,frame=src,input=src,form=fakeentry" [Assertion] zend.assertions = -1 [mbstring] [gd] [exif] [Tidy] tidy.clean_output = Off [soap] soap.wsdl_cache_enabled=1 soap.wsdl_cache_dir="/tmp" soap.wsdl_cache_ttl=86400 soap.wsdl_cache_limit = 5 [sysvshm] [ldap] ldap.max_links = -1 [mcrypt] [dba] [curl] [openssl] [opcache] opcache.enable=1 opcache.memory_consumption=128 opcache.interned_strings_buffer=8 opcache.max_accelerated_files=4000 opcache.revalidate_freq=60 opcache.fast_shutdown=1 opcache.validate_timestamps=0 opcache.file_cache=/tmp{/card-describe}php安装后测试php -v php --modules整体功能测试echo '<?php phpinfo(); ?>' > index.php通过浏览器访问出现下图界面，即php安装正常{card-default label="php测试" width="50%"}{/card-default}安装配置mysql数据库服务{card-default label="视频2" width="80%"}{bilibili bvid="BV1C54y1p7eY" page="wordpress博客配置"/}{/card-default}清除默认的maradbyum list installed | grep mariadb yum -y remove mariadb-libs.x86_64安装依赖包yum install -y perl.x86_64 libaio.x86_64 net-tools.x86_64 perl-Compress-Raw-Bzip2 perl-Compress-Raw-Zlib perl-DBI perl-IO-Compress perl-Net-Daemon perl-PlRPC下载mysql安装包,并安装wget http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-5.6/MySQL-client-5.6.51-1.el7.x86_64.rpm wget http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-5.6/MySQL-server-5.6.51-1.el7.x86_64.rpm wget http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-5.6/MySQL-devel-5.6.51-1.el7.x86_64.rpm yum localinstall MySQL-*配置 mysqlvim /etc/my.cnf配置内容隐藏内容，请前往内页查看详情创建目录，修改目录权限mkdir -p /data/mysql/ /data/logs/ /data/logs/mysql/ chown -R mysql:mysql /data/logs/mysql/数据库初始化mysql_install_db --user=mysql --datadir=/data/mysql systemctl start mysql /usr/bin/mysqladmin -u root password -S /data/mysql/mysql.sock查看mysql服务状态systemctl status mysql安装完成安装配置wordpress博客{card-default label="视频3" width="80%"}{bilibili bvid="BV19t4y1q77A" page="wordpress博客配置"/}{/card-default}下载安装包wget https://wordpress.org/wordpress-5.6.tar.gz wget https://wp101.net/wordpress-5.6.tar.gz解压tar xvf wordpress-5.6.tar.gz sudo mv wordpress /var/www/html/mywordpress/更改代码目录权限chown -R apache:apache /var/www/html/mywordpress/修改配置文件httpd服务的配置vim /etc/httpd/conf/httpd.conf配置内容隐藏内容，请前往内页查看详情重启httpd服务，使配置生效systemctl restart httpd建库授权CREATE DATABASE `wpdb` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; create user 'wpuser'@'localhost' IDENTIFIED BY 'wpuser123'; grant all privileges on wpdb.* to 'wpuser'@'localhost'; flush privileges;绑定域名到指定IPecho '192.168.88.2 www.mywordpress.com' >> /etc/hosts通过浏览器访问,并配置博客http://www.mywordpress.com/FAQ关闭自动更新//wp-config.php define('AUTOMATIC_UPDATER_DISABLED',true);

mariadb数据库安装配置 {card-default label="mysql数据库" width="80%"}{/card-default}前言{callout color="#f0ad4e"}但是mariadb与mysql在使用范围和使用目的上都有所不同，两者的主要功能都是为了通过开源社区在维护中，从而获得GPL授权许可。从关系上来看，mariadb是属于数据库管理系统的开发和升级版本，它仅仅代表MySQL的一个分支。{/callout}{card-default label="版本信息" width="100%"}系统版本：centos7.9软件版本：mariadb 10.3.28{/card-default}安装mariadb(单机版)直接安装到centos7.9系统内配置yum源编辑配置文件vim /etc/yum.repos.d/MariaDB.repo文件内容[mariadb] name = MariaDB baseurl = http://yum.mariadb.org/10.3.28/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1安装yum install MariaDB-server MariaDB-devel MariaDB-shared配置文件编辑配置文件vim /etc/my.cnf.d/mariadb-server.cnf配置内容隐藏内容，请前往内页查看详情创建数据目录mysql -p /data/log/mariadb/ && chown -R mysql:mysql /data/log/mariadb/ mkdir -p /run/mariadb/ && chown -R mysql:mysql /run/mariadb/数据库初始化mysql_install_db chown -R mysql:mysql /data/mysql/启动systemctl enable --now mariadb安全设置mysql_secure_installationdocker运行mariadb安装docker和docker-compose docker和docker-compose一键安装编写docker-compose.yamlversion: '3.5' services: mariadb: container_name: mariadb image: mariadb:10.3.28 ports: - "3306:3306" volumes: - /etc/localtime:/etc/localtime:ro - ./conf/my.cnf:/etc/mysql/my.cnf - /data/mysqldb:/var/lib/mysql environment: MYSQL_ROOT_PASSWORD: xxxxxxx@123 TIME_ZONE: Asia/Shanghai restart: always networks: - mariadb_net networks: mariadb_net: driver: bridge driver_opts: com.docker.network.enable_ipv6: "false" ipam: driver: default config: - subnet: 172.16.237.0/24启动docker-compose up -dmariadb所有配置下载地址隐藏内容，请前往内页查看详情双机主主模式配置基于binlog的传统模式启动数据库#m1执行 cd /opt/mariadb-m-m/m1/ docker-compose up -d #m2执行 cd /opt/ly-traffic/mariadb-m-m/m2/ docker-compose up -d进入m1节点执行#进入mariadb docker exec -it mariadb_m1 /bin/bash mysql -uroot -pUtemyan001db -h 127.0.0.1 #确认系统时间 select now(); #确认字符集 show variables like '%character%'; #查看file和positon show master status; 图1 #创建同步账号 grant replication slave,replication client on *.* to 'slave'@'49.119.119.%' identified by "slave@123"; flush privileges; #同步配置 change master to master_host='mariadb_m2',master_user='slave',master_password='slave@123',master_port=3306,master_log_file='mysql-bin.000003', master_log_pos=342,master_connect_retry=30; #启动 start slave; #查看slave状态； show slave status\G进入m2执行docker exec -it mariadb_m2 /bin/bash mysql -uroot -pUtemyan001db -h 127.0.0.1 #检查系统时间 select now(); #确认字符集 show variables like '%character%'; #查看file和positon show master status; 图1 #创建同步账号 grant replication slave,replication client on *.* to 'slave'@'49.119.119.%' identified by "slave@123"; flush privileges; #同步配置 change master to master_host='mariadb_m1',master_user='slave',master_password='slave@123',master_port=3306,master_log_file='mysql-bin.000003', master_log_pos=342,master_connect_retry=30; #启动 start slave; #查看slave状态； show slave status\G{card-default label="图一" width="80%"}{/card-default}{card-default label="配置完成" width="80%"}{/card-default}完成后测试#m1创建数据库 create database test1; #m2 查看 show database; #m2创建数据库 create database test2; #m1 查看 show database; #关闭m2,m1创建数据库test3，test2数据库创建表，然后启动m2 看是否自动同步基于GTID模式m1到m2的复制#m1 执行 #查看master状态 show master status; 图3 #查看gtid_binlog show variables like '%gtid_binlog%'; #m2执行 #设置slave_pos set global gtid_slave_pos='0-11-7192'; #配置master change master to master_host='mariadb_m1',master_user='slave',master_password='slave@123',master_port=3306,master_use_gtid=slave_pos,master_connect_retry=30; #启动 start slave;{card-default label="图3" width="80%"}{/card-default}m2到m1的复制#m1配置 set global gtid_slave_pos='0-12-7192'; #配置master change master to master_host='mariadb_m2',master_user='slave',master_password='slave@123',master_port=3306,master_use_gtid=slave_pos,master_connect_retry=30; #启动 start slave;配置完成其他安装客户端yum install mariadb创建数据库CREATE DATABASE `xwaf` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; create user 'xwafuser'@'127.0.0.1' IDENTIFIED BY 'xwafuser@123'; grant all privileges on xwaf.* to 'xwafuser'@'127.0.0.1'; flush privileges;

mysql数据库全量备份方案 {card-default label="数据备份" width="90%"}{/card-default}全量备份脚本dbback.sh#!/bin/bash ##备份数据库脚本 ##email: 1940728253@qq.com ##xwzy1130 #MySQL User Information HOST=127.0.0.1 USERNAME=root PASSWORD=***** #Date Format DATE=`date +%Y%m%d` #Back directory DAYS=20 #备份文件存放位置 BACKUP_DIR=/data/db-backup/ #MySQL directory MYSQL_DIR=/usr/bin/ #Go to the backup directory if [ -d ${BACKUP_DIR} ];then cd ${BACKUP_DIR} else mkdir -p ${BACKUP_DIR} fi #The first instance of the backup while read dbname do if [ -f ${dbname}_${DATE}.gz ]; then echo "MySQL Database ${dbname}_${DATE}.gz already exists." else ${MYSQL_DIR}/mysqldump -u${USERNAME} -p${PASSWORD} -h${HOST} --default-character-set=utf8 --master-data=2 --single-transaction ${dbname} | /bin/gzip > ${BACKUP_DIR}/${dbname}_${DATE}.gz fi done < /opt/ly-traffic/db-backup/dbs.txt ##删除旧备份文件 find ${BACKUP_DIR} -name "*.gz" -type f -mtime +22|tee -a ${BACKUP_DIR}/del.log|xargs rm -r 2>>${BACKUP_DIR}/err.log exit 0数据备份可用行测试gzid -d 文件名 mysql -uroot -p -h127.0.0.1 数据库 < 文件名