一、实时查看sql语句:
mysql> SHOW VARIABLES LIKE "general_log%";
mysql> set global general_log_file="/var/lib/mysql/geneeral_log.log";
mysql> set global general_log=on;
$ tail -f /var/lib/mysql/geneeral_log.log|grep gpsdata
二、卸载、重装mysql
1.列出安装包
[root@localhost sysbench]# yum list installed mysql*
已加载插件:fastestmirror, langpacks
Determining fastest mirrors
* base: mirrors.aliyun.com
* elrepo: ftp.yz.yamagata-u.ac.jp
* epel: nrt.edge.kernel.org
* extras: mirrors.njupt.edu.cn
* updates: mirrors.aliyun.com
已安装的软件包
mysql-community-client.x86_64 8.0.21-1.el7 installed
mysql-community-common.x86_64 8.0.21-1.el7 installed
mysql-community-libs.x86_64 8.0.21-1.el7 installed
mysql-community-libs-compat.x86_64 8.0.21-1.el7 installed
mysql-community-server.x86_64 8.0.21-1.el7 installed
[root@localhost sysbench]#
不推荐:rpm -qa|grep -i mysql
这个方式找到的会找到其他相关的,作为第一个无结果时可选项。
2.关闭mysql
[root@localhost sysbench]# systemctl stop mysqld
[root@localhost sysbench]#
3.清除安装包,并删除相关目录
[root@localhost sysbench]# rpm -e --nodeps mysql-libs-5.1.71-1.el6.x86_64
错误:未安装软件包 mysql-libs-5.1.71-1.el6.x86_64
[root@localhost sysbench]# rpm -e --nodeps mysql-community-client.x86_64
[root@localhost sysbench]# rpm -e --nodeps mysql-community-common.x86_64
[root@localhost sysbench]# rpm -e --nodeps mysql-community-libs.x86_64
[root@localhost sysbench]# rpm -e --nodeps mysql-community-libs-compat.x86_64
[root@localhost sysbench]# rpm -e --nodeps mysql-community-server.x86_64
警告:文件 /var/lib/mysql: 移除失败: 没有那个文件或目录
警告:/etc/my.cnf 已另存为 /etc/my.cnf.rpmsave
[root@localhost sysbench]# systemctl stop mysqld
Failed to stop mysqld.service: Unit mysqld.service not loaded.
[root@localhost sysbench]#
4.重装
转移datadir要给权限
rpm -ivh ./sqlRPM/*.rpm
chown -R mysql:mysql /home/mysql/
之后
①创建用户
②创建库表
5.完整操作
@卸载
[root@localhost base74]# ls
base74.sh expand httpd.conf my.cnf php-74.ini phpRPM selinux sqlRPM vhost.conf vsftpd.conf
[root@localhost base74]# yum list installed mysql*
已加载插件:fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.njupt.edu.cn
* elrepo: ftp.yz.yamagata-u.ac.jp
* epel: nrt.edge.kernel.org
* extras: mirrors.njupt.edu.cn
* updates: mirrors.aliyun.com
已安装的软件包
mysql-community-client.x86_64 8.0.21-1.el7 installed
mysql-community-common.x86_64 8.0.21-1.el7 installed
mysql-community-libs.x86_64 8.0.21-1.el7 installed
mysql-community-libs-compat.x86_64 8.0.21-1.el7 installed
mysql-community-server.x86_64 8.0.21-1.el7 installed
[root@localhost base74]# systemctl stop mysqld
[root@localhost base74]# rpm -e --nodeps mysql-community-client.x86_64
[root@localhost base74]# rpm -e --nodeps mysql-community-common.x86_64
[root@localhost base74]# rpm -e --nodeps mysql-community-libs.x86_64
[root@localhost base74]# rpm -e --nodeps mysql-community-libs-compat.x86_64
[root@localhost base74]# rpm -e --nodeps mysql-community-server.x86_64
警告:/etc/my.cnf 已另存为 /etc/my.cnf.rpmsave
[root@localhost base74]# cd /home/mysql/
[root@localhost mysql]# ls
mysql mysqlTuner 数据库存储目录
[root@localhost mysql]# cd mysql
[root@localhost mysql]# ls
1 binlog.000003 binlog.000007 binlog.000011 binlog.000015 binlog.000019 binlog.000023 ca-key.pem #ib_16384_0.dblwr ib_logfile0 media performance_schema server-key.pem
auto.cnf binlog.000004 binlog.000008 binlog.000012 binlog.000016 binlog.000020 binlog.000024 ca.pem #ib_16384_1.dblwr ib_logfile1 mysql private_key.pem sys
binlog.000001 binlog.000005 binlog.000009 binlog.000013 binlog.000017 binlog.000021 binlog.000025 client-cert.pem ib_buffer_pool #innodb_temp mysqld.log public_key.pem undo_001
binlog.000002 binlog.000006 binlog.000010 binlog.000014 binlog.000018 binlog.000022 binlog.index client-key.pem ibdata1 localhost-slow.log mysql.ibd server-cert.pem undo_002
[root@localhost mysql]# du -hls ./
138G ./
[root@localhost mysql]# 8000w 138G
@安装
安装包,去官网下即可
[root@localhost sqlRPM]# ls
mysql-community-client-8.0.21-1.el7.x86_64.rpm mysql-community-libs-8.0.21-1.el7.x86_64.rpm mysql-community-server-8.0.21-1.el7.x86_64.rpm
mysql-community-common-8.0.21-1.el7.x86_64.rpm mysql-community-libs-compat-8.0.21-1.el7.x86_64.rpm
[root@localhost sqlRPM]# rpm -ivh ./*.rpm
警告:./mysql-community-client-8.0.21-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中... ################################# [100%]
正在升级/安装...
1:mysql-community-common-8.0.21-1.e################################# [ 20%]
2:mysql-community-libs-8.0.21-1.el7################################# [ 40%]
3:mysql-community-client-8.0.21-1.e################################# [ 60%]
4:mysql-community-server-8.0.21-1.e################################# [ 80%]
5:mysql-community-libs-compat-8.0.2################################# [100%]
[root@localhost sqlRPM]#
@切换目录
安装完成,立即启动,创建账号;然后复制目录后,改权限,关闭旧的,启动新的
[root@localhost sqlRPM]# rm -rf /home/mysql/mysql
[root@localhost sqlRPM]# cp -rf /var/lib/mysql /home/mysql/
[root@localhost sqlRPM]# vim /etc/my.cnf
[root@localhost sqlRPM]# chown -R mysql:mysql /home/mysql/mysql
[root@localhost sqlRPM]# systemctl stop mysqld
[root@localhost sqlRPM]# systemctl start mysqld
[root@localhost sqlRPM]#
@config
[mysqld]
datadir=/home/mysql/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=65535
explicit_defaults_for_timestamp
server_id=186
#lower_case_table_names=1
character_set_server=utf8mb4
safe_user_create
max_connections=3000
max_user_connections=2980
secure_file_priv=/home/mysql/mysql
max_connect_errors=100000
interactive_timeout=86400
sync_binlog=100
back_log=1024
max_binlog_cache_size=2G
max_binlog_size=512M
#default_storage_engine=InnoDB
log_slave_updates=1
log_bin=mysql-bin
binlog_format=mixed
binlog_cache_size=32M
max_binlog_size=512M
long_query_time=1
log_output=File
log_error=/var/log/mysql-error.log
slow_query_log=1
slow_query_log_file=/home/mysql/mysql/localhost-slow.log
log_queries_not_using_indexes=0
log_slave_updates=ON
log_slow_admin_statements=1
general_log=0
general_log_file=/home/mysql/mysql
binlog_expire_logs_seconds = 1728000
relay_log=/home/mysql/mysql/relay-bin
relay_log_index=/home/mysql/mysql/relay-bin.index
binlog-do-db=media
############ 主从
server-id=186
log-bin=mysql-bin
##master_info_repository=media
#relay-log-info-repository=media
relay-log-recovery
#
innodb_adaptive_flushing=ON
innodb_flush_log_at_trx_commit=2
innodb_max_dirty_pages_pct=60
innodb_open_files=60000
innodb_purge_threads=1
innodb_read_io_threads=4
innodb_stats_on_metadata=OFF
innodb_flush_method=O_DIRECT
[mysql]
no-auto-rehash
default-character-set=utf8mb4
net-buffer-length=64K
unbuffered
max-allowed-packet=2G
[mysqldump]
quick
max_allow_packet=2G
log_error=/home/mysql/mysql/mysql_dump_error.log
net_buffer_length=8K