vlambda博客
学习文章列表

# /etc/sysctl.conf#以下参数根据需求调整# 端口最大的监听队列长度net.core.somaxconn=65535net.core.netdev_max_backlog=65535net.ipv4.tcp_max_syn_backlog=65535



net.ipv4.tcp_tw_recycle=1net.ipv4.tcp_tw_reuse=1net.ipv4.tcp_fin_timeout=10

#调整tcp连接缓冲区的默认值和最大值net.core.wmem_default=87380net.core.rmem_default=87380net.core.rmem_max=16777216net.core.wmem_max=16777216

#用于减少失效tcp连接占用的资源，加快资源回收的效率net.ipv4.tcp_keepalive_time=120net.ipv4.tcp_keepalive_intvl=30net.ipv4.tcp_keepalive_probes=3

#Linux内核中最重要的参数之一，用于定义单个共享内存段的最大值#注意：#1、这个参数应该设置为足够大，以便能在一个共享内存段下容纳下整个Innodb缓冲池的大小#2、这个值的大小对于64位Linux系统，可取的最大值为物理内存值减 -1 byte,建议值为大于物理内存的一半，一般取值大于Innodb缓冲池的大小即可，可以取物理内存减 -1 bytekernel.shmmax=4294967295

#这个参数当内存不足时，会对性能产生比较明显的影响#设置为0表示：告诉Linux内核除非虚拟内存完全占满了，否则不要使用交换分区swapvm.swappiness=0

#加到/etc/security/limits.conf 文件末尾 * soft nofile 65535 * hard nofile 65535 * soft nproc 65535 * hard nproc 65535

cat /sys/block/sda/queue/scheduler # vm虚化的系统cat /sys/block/vda/queue/scheduler # kvm虚化的系统

[root@zhdc-zht-mysql-master support-files]# fsck -N /datafsck，来自 util-linux 2.23.2[/sbin/fsck.xfs (1) -- /data] fsck.xfs /dev/mapper/datavg-datalv

NAME FSTYPE LABEL UUID MOUNTPOINTsr0vda├─vda1 xfs 7840c331-cc73-4f1e-869b-7db63a0a6343 /boot└─vda2 LVM2_member SufBpu-svy9-3abr-DGVC-c5Xb-5I1j-pfkH8M ├─centos-root xfs 47351f56-9b32-430f-b27f-ecf55b90dc96 / ├─centos-swap swap 01111d19-4ecc-4c5e-a099-8bffa16b700f └─centos-home xfs 37a2e559-996d-4e7f-a433-dff66dd57aca /homevdb└─vdb1 vfat config-2 07CB-3E11vdc LVM2_member feDD65-vsf0-CQ95-oAfM-p2gr-jmbb-zppuJk└─datavg-datalv xfs 58125523-299a-450d-8c6f-8d8116e0c7c4 /datavdd LVM2_member d0KxOn-6SDT-X7Hz-B5Ng-O3aQ-nXEm-FT9VYw└─centos-root xfs 47351f56-9b32-430f-b27f-ecf55b90dc96 /

systemctl status firewalld.service

groupadd mysqluseradd -r -g mysql mysqlchown -R mysql.mysql /usr/local/mysql

# 解压tar -xzvf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz /usr/local/# 用户groupadd mysqluseradd -r -g mysql mysqlchown -R mysql.mysql /usr/local/mysql

su mysql # 切换mysql用户/usr/local/mysql/bin/mysqld --user=mysql --basedir=/data/mysql/ --datadir=/data/mysql/data --initialize

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqlcp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

[mysqld]

datadir=/data/mysql/databasedir=/usr/local/mysqlsocket=/tmp/mysql.sockuser=mysqlport=3306character-set-server=utf8max_connections=2000max_connect_errors=10000

sql_mode='NO_AUTO_CREATE_USER'# 取消密码验证# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

## include all files from the config directory#!includedir /etc/my.cnf.d

chkconfig mysql on

netstat-lntup|grep 3306

[root@mysql init.d]# netstat -lntup|grep 3306tcp6 0 0 :::3306 :::* LISTEN 18030/mysqld

mysqld_safe --defaults-file=/etc/my.cnfservice mysqld start

mysqladmin -S /tmp/mysql.sock password
# 无需密码提示：ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)# 需要密码提示：ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[mysqld]skip-grant-tables

update mysql.user set authentication_string='zhdc530' where user='root';update mysql.user set authentication_string=PASSWORD('zhdc530') where user='root';-- 刷新数据库权限flush privileges;

alter user user() identified by "XXXXXX";

slow_query_log = ONslow_query_log_file = /usr/local/mysql/data/slow.loglong_query_time = 1

sql_mode='NO_AUTO_CREATE_USER'

innodb_buffer_pool_size=8Gtransaction_isolation = READ-COMMITTEDinnodb_flush_method = O_DIRECT

open_files_limit = 65535back_log = 1024

interactive_timeout = 28800wait_timeout = 3600

tmp_table_size = 32Mmax_heap_table_size = 32M

# binloglog-bin=coli-office-binlogbinlog-format=ROWserver_id=1binlog_cache_size = 4Mmax_binlog_cache_size = 2Gmax_binlog_size = 1Gexpire_logs_days = 5master_info_repository = TABLErelay_log_info_repository = TABLE

show binary logs #获取binlog文件日志列表show master status # 查看当前正在写入的binlog文件show master logs # 查看master上的binlog文件show binlog events #查看第一个binlog文件内容show binlog events 'mysql-bin.000002' # 查看指定binlog文件内容， 如：查看mysql-bin.000002文件内容

CREATE USER 'repl'@'10.6.32.140' IDENTIFIED BY 'colioffice@slave@123';#创建用户
update mysql.user set authentication_string=PASSWORD('colioffice@slave@123') where User='repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.6.32.140';#分配权限flush privileges; #刷新权限

SHOW MASTER STATUS;

CHANGE MASTER TO MASTER_HOST='10.6.32.152', MASTER_USER='repl', MASTER_PASSWORD='colioffice@slave@123', MASTER_LOG_FILE='coli-office-binlog.000004', MASTER_LOG_POS=154;
start slave; # 启动slave同步进程

show slave status\G

mysql -u root -p < /data/zht_20220223030001.sql# 输入密码

[root@mysql-master ~]# cat /proc/versionLinux version 3.10.0-1062.4.3.el7.x86_64 ([email protected]) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-39) (GCC) ) #1 SMP Wed Nov 13 23:58:53 UTC 2019[root@mysql-master ~]# uname -aLinux zhdc-zht-mysql-master 3.10.0-1062.4.3.el7.x86_64 #1 SMP Wed Nov 13 23:58:53 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux[root@mysql-master ~]# file /bin/bash/bin/bash: ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.32, BuildID[sha1]=7e644dee920bc3ba797c38e05383286563712b49, stripped

mv mysqld_exporter-0.11.0.linux-amd64 /usr/local/mysql_exportercd /usr/local/mysql_exporter

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'hello@exporter';GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';flush privileges; #刷新权限

[client]user=exporterpassword=hello@exporter

# 选择采集innodb--collect.info_schema.innodb_cmp # innodb存储引擎状态--collect.engine_innodb_status # 指定配置文件--config.my-cnf=".my.cnf"

[Unit]Description=https://prometheus.io

[Service]Restart=on-failureExecStart=/usr/local/mysql_exporter/mysqld_exporter --config.my-cnf=/usr/local/mysql_exporter/.my.cnf 

[Install]WantedBy=multi-user.target

systemctl daemon-reloadsystemctl restart mysql_exporter.service