Skip to content

MySQL相关

Jump to bottom
Kervin edited this page Aug 15, 2023 · 18 revisions

一.MySQL配置(Windows)

  1. 下载 纯净版 完全版
  2. Windows安装/卸载
"D:\MySQL8\bin\mysqld.exe" --install  MySQL8 --defaults-file="D:\MySQL8\my.ini"
"D:\MySQL8\bin\mysqld.exe" --remove  MySQL8
  1. Windows安装
  • 纯净版安装

    可以自定义路径

  • 完全版安装

    自定义用户以及配置

  • root登录（MySQL8）

    alter user 'root'@'localhost' identified with mysql_native_password by 'root';

二.MySQL数据处理

  1. MySQL远程导入导出
mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2

ex.
mysqldump --host=192.168.80.137 -uroot -proot -C --databases test |mysql --host=192.168.80.133 -uroot -proot test
  1. 服务器导出
mysqldump -uroot -proot --all-databases >/tmp/all.sql
mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql
mysqldump -uroot -proot --databases db1 --tables a1 a2  >/tmp/db1.sql
mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1'  >/tmp/a1.sql
mysqldump -uroot -proot --no-create-info --databases db1 --tables a1 --where="id='a'"  >/tmp/a1.sql
mysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql
mysqldump -uroot --add-drop-table db1>/home/user/db2.sql;
  1. 服务器导入
use db2;
source /home/user/db2.sql;

参考:(http://www.cnblogs.com/chenmh/p/5300370.html)

  1. 批量删除进程
select concat('KILL ',id,';') from information_schema.processlist  into outfile '/home/kill_sql.sql';
source /home/kill_sql.sql

三.MySQL集群

3.MySQL

卸载

apt-get autoremove --purge -y mysql-server
apt-get autoremove --purge -y mysql-server-*
apt-get autoremove --purge -y mysql-client
apt-get autoremove --purge -y mysql-client-*
apt-get remove -y mysql-common
dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P 
rm -rf /etc/mysql
rm -rf /var/lib/mysql
apt autoremove -y
apt autoclean -y

rm -rf /var/lib/dpkg/info
mkdir /var/lib/dpkg/info 
apt-get upgrade

集群卸载

rm -rf /usr/local/bin/ndb*
rm -rf /etc/rc.d/init.d/mysql.server
rm -rf /etc/init.d/mysqld
rm -rf /usr/local/mysql
rm -rf /usr/local/mysql-cluster-8.0.21-linux-glibc2.12-x86_64/
rm -rf /var/lib/mysql-cluster

安装

apt-get install mysql-server mysql-client
netstat -tap | grep mysql

配置root用户

mysql -u root -p

INSERT INTO `mysql`.`user`(`Host`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `Event_priv`, `Trigger_priv`, `Create_tablespace_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`, `plugin`, `authentication_string`, `password_expired`, `password_last_changed`, `password_lifetime`, `account_locked`) VALUES ('%', 'root', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, 'mysql_native_password', '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B', 'N', '2019-10-14 13:29:57', NULL, 'N');


update mysql.user set authentication_string=password('root') where user='root';
grant all on *.* to root@'%' identified by 'root' with grant option;
grant grant option on *.* to `root`@`%`;
flush privileges;

MySQL8

INSERT INTO `mysql`.`user` (`Host`, `User`, `ssl_cipher`, `x509_issuer`,`x509_subject`,`plugin`, `authentication_string`) VALUES ('%', 'root', '','','','mysql_native_password', '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B') ;
flush privileges;
grant all on *.* to root@'%';
grant grant option on *.* to `root`@`%`;
flush privileges;

配置外网访问

vim /etc/mysql/mysql.conf.d/mysqld.cnf
注掉 bind-address		= 127.0.0.1
service mysql restart

优化

server-id       = 101
log-bin = mysql-bin
binlog-format   = ROW
max_connections = 1000
connect_timeout = 30
default-time-zone       = '+8:00'
max_binlog_size = 100M
sync_binlog     = 500
innodb_flush_log_at_trx_commit  = 2

log_timestamps                 = SYSTEM
lower_case_table_names         = 1
expire_logs_days        = 3

skip-host-cache
skip-name-resolve

注:8.0不支持lower_case_table_names且expire_logs_days为binlog_expire_logs_seconds = 259200

#mysql8.0+若支持lower_case_table_names执行如下操作
rm -rf /var/lib/mysql/*
/usr/sbin/mysqld --initialize-insecure --user=mysql --lower-case-table-names=1

MySQL数据目录:/var/lib/mysql

systemctl restart mysql
systemctl status mysql

集群

1) 解压安装

所有机器上执行

mkdir -p /usr/local/mysql
tar -C /usr/local/mysql -zxvf mysql-cluster-8.0.21-linux-glibc2.12-x86_64.tar.gz
cd /usr/local/mysql
mv mysql-cluster-8.0.21-linux-glibc2.12-x86_64 mysql-cluster
cp -vr mysql-cluster ./mysqld

#创建mysql用户及组
groupadd mysql
useradd -g mysql -s /bin/false mysql
  • 数据/SQL节点
cd /usr/local/mysql/mysqld
# 初始化数据
./bin/mysqld  --initialize-insecure --lower-case-table-names=1
# 注：lower-case-table-names=1必须在my.cnf中体现
#授权
echo "">error.log
chown -R root .
chown -R mysql data
chgrp -R mysql .
chmod -R 777 data
chmod -R 777 error.log

cp bin/ndbd /usr/local/bin/ndbd
cp bin/ndbmtd /usr/local/bin/ndbmtd
cp bin/ndb_config /usr/local/bin
chmod +x /usr/local/bin/ndb*
  • 管理节点ndb
cd /usr/local/mysql/mysql-cluster
cp bin/ndb_mgm* /usr/local/bin

chmod +x /usr/local/bin/ndb_mgm*
mkdir -p /usr/local/mysql/mysql-cluster/mgrdata
mkdir -p /usr/local/mysql/mysql-cluster/data
mkdir -p /usr/local/mysql/mysql-cluster/BACKUP/mgrdata
mkdir -p /usr/local/mysql/mysql-cluster/BACKUP/data

2) 配置

  • 管理节点(/usr/local/mysql/mysql-cluster/cluster.cnf)
[NDBD DEFAULT]    
NoOfReplicas=3
TcpBind_INADDR_ANY=true
DataMemory=2G
MinFreePct=20
StringMemory=50
MaxAllocate=512M
DiskSyncSize=4M
MaxNoOfTables=20320
MaxNoOfAttributes=100000
MaxNoOfOrderedIndexes=3072
MaxNoOfConcurrentTransactions=409600
MaxNoOfConcurrentOperations=3276800
MaxDiskWriteSpeed=100M
SchedulerExecutionTimer=0
DataDir=/usr/local/mysql/mysql-cluster/data
BackupDataDir=/usr/local/mysql/mysql-cluster/BACKUP/data

# Mgr Node Default Options
[NDB_MGMD DEFAULT]
DataDir=/usr/local/mysql/mysql-cluster/mgrdata
# Mgr Node Options
[NDB_MGMD]
HostName=ics-server160
[NDB_MGMD]
HostName=ics-server161
[NDB_MGMD]
HostName=ics-server162

# Data Node Options
[NDBD]
HostName=ics-server160
[NDBD]                  
HostName=ics-server161
[NDBD]                  
HostName=ics-server162

# SQL Node Options
[MYSQLD]               
HostName=ics-server160
[MYSQLD]
HostName=ics-server161
[MYSQLD]
HostName=ics-server162
  • 数据/SQL节点(/usr/local/mysql/mysqld/my.cnf)
#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld]
# cluster connect
ndbcluster   
ndb-connectstring=172.21.32.36,172.21.32.37,172.21.32.38

default_storage_engine  =ndbcluster
default_tmp_storage_engine =ndbcluster
#
# * Basic Settings
#
user		= mysql
# pid-file	= /var/run/mysqld/mysqld.pid
# socket	= /var/run/mysqld/mysqld.sock
port		= 3306
datadir	= /usr/local/mysql/mysqld/data


# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir		= /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address		= 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size		= 16M
# max_allowed_packet	= 64M
# thread_stack		= 256K

# thread_cache_size       = -1

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
#myisam-recover-options  = BACKUP

# max_connections        = 151

# table_open_cache       = 4000

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file        = /var/log/mysql/query.log
# general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /usr/local/mysql/mysqld/error.log
#
# Here you can see queries with especially long duration
# slow_query_log		= 1
# slow_query_log_file	= /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
# server-id		= 1
# log_bin			= /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds	= 2592000
#server-id       = 100
log-bin = mysql-bin
#binlog-format   = ROW
max_connections = 1000
connect_timeout = 30
default-time-zone       = '+8:00'
max_binlog_size = 100M
sync_binlog     = 500
innodb_flush_log_at_trx_commit  = 2

log_timestamps                 = SYSTEM
lower_case_table_names         = 1
binlog_expire_logs_seconds      = 259200

innodb_lock_wait_timeout	=500
log_bin_trust_function_creators =1

eq_range_index_dive_limit=100000

tmp_table_size = 1G
max_heap_table_size = 1G

max_prepared_stmt_count=16382
# 0-8M,default:1K
max_sort_length=1K
# 32K-18446744073709551615,default:256K
sort_buffer_size=256K
# 1K-1M ,default:16K
net_buffer_length=1M
# ulimit -n
open_files_limit=5000
# 1K-3G,default:8K
query_alloc_block_size=8K
# 1K-128K.default:8K
transaction_alloc_block_size=128K
# 8K-18446744073709551615,default:8K
query_prealloc_size=16M
# select_into_buffer_size|sql_buffer_result, support_version:8.0.22+
# 8K-1G,default:128K
select_into_buffer_size=128K
sql_buffer_result=ON
# This variable is available only if the thread pool plugin is enabled.
# 1-512,default:16
thread_pool_size=512
# This variable is available only if the thread pool plugin is enabled.
# 4-600,default:6
thread_pool_stall_limit=600


skip-host-cache
skip-name-resolve
# binlog_do_db		= include_database_name
# binlog_ignore_db	= include_database_name

注： server-id对应每个服务器不一样

3) 启动

  • 管理节点
mkdir -p /usr/local/mysql/mysql-cluster/data
mkdir -p /usr/local/mysql/mysql-cluster/mgrdata
ndb_mgmd -f /usr/local/mysql/mysql-cluster/cluster.cnf --ndb-nodeid=1 --initial --reload
# 参数  --ndb-nodeid=1(节点ID) --reload(节点重新加载) --initial(初始化或重新初始化)

查看集群状态

ndb_mgm -e SHOW

关闭

ndb_mgm -e shutdown

注：若错误信息：ndb_mgmd: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory，解决方案如下：

#查看依赖
ls /lib/x86_64-linux-gnu/libtinfo.so.*
# 检索本地依赖
apt install mlocate
locate libtinfo.so.5
#从检索目录拷贝
cp /snap/core/7917/lib/x86_64-linux-gnu/libtinfo.so.5 /lib/x86_64-linux-gnu/

查看集群参数值

ndb_config --diff-default --query=type,MaxNoOfConcurrentOperations,MaxNoOfConcurrentTransactions
  • 数据节点
ndbd --ndb-connectstring=172.21.32.36,172.21.32.37,172.21.32.38
  • SQL节点
cd /usr/local/mysql/mysqld
cp support-files/mysql.server ./mysql
chmod +x mysql
#修改数据路径(vim mysql)中的basedir=/usr/local/mysql/mysqld
cp mysql /etc/init.d/
chmod +x /etc/init.d/mysql

#启动
./mysql start

#添加访问用户
./bin/mysql -hlocalhost -uroot

启动报错：Could not open unix socket lock file /tmp/mysql.sock.lock 则删除/tmp/mysql*

四.MySQL常见

  1. 查询表名
select table_name from information_schema.tables where table_schema='database1' and table_name not in('table1')
  1. 定时器
delimiter $$
create event channel_test_event  
on schedule every 1 second starts timestamp '2018-12-06 18:00:10'
do
begin
    start transaction;
    set @timenow=now(); #开始事务

		INSERT INTO `channel_test` (`sp_id`, `product_type`, `channel_id`, `name`, `price`, `notice`, `source_segment`, `static_ext`, `support_cmcc`, `support_unicom`, `support_telecom`, `support_international`, `support_area`, `msg_index_type`, `mo_match_type`, `have_report`, `have_mo`, `size_max`, `size_first`, `size_charge`, `cover_key`, `submit_begin`, `submit_end`, `channel_status`, `baobei_model`, `sign_must_ext`, `allow_user_ext`, `auto_ext_src_prefix`, `auto_ext_src_length`, `save_model`, `pay_mode`, `limit_count`, `sent_count`, `charge_count`, `balance_alert_min`, `balance_alert_mails`, `balance_alert_mobiles`, `pause_count_min`) VALUES (1, 'sms', 130, concat('短信网关canal统计',NOW()), 3, '', '106942113', '', 'no', 'yes', 'no', 'no', '', 'multi', 'dest', 'yes', 'yes', 500, 70, 67, 0, '0000', '2400', 'normal', 'not_required', 'yes', 'no', '4', 2, 'single_thread', 'pre', 2521478, 0, 0, 100000, 'zhaoyu@spacei.com.cn  zhangling@spacei.com.cn  zhangfeini@spacei.com.cn', '15001210433 18710121898 13716794535  18311196021', 0);
    # update
    update channel_test set name=concat('update-短信网关canal统计',NOW()) where id<10000 and id>242;

    commit;  #提交事务
end  $$

#查看当前是否已开启事件调度器
#show variables like 'event_scheduler';
# drop index channel_id on channel_test;
#要想保证能够执行event事件，就必须保证定时器是开启状态，默认为关闭状态
#set global event_scheduler =1;或者set GLOBAL event_scheduler = ON;
#
# 停止
#ALTER EVENT channel_test_event DISABLE;
# 开启
#alter event channel_test_event enable;
# 查看状态
#select * from mysql.event

delimiter ;
  1. 其他问题及解决方案
  • 问题1：
 * MySQL server PID file could not be found!
Starting MySQL
 * Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe)

解决办法： 修改mysql.server中basedir为MySQL目录

  • 问题2：
 mysqldump: Couldn't execute 'SELECT COLUMN_NAME,JSON_EXTRACT(HISTOGRAM, '$."number-of-buckets-specified"')FROM information_schema.COLUMN_STATISTICS
': Unknown table 'column_statistics' in information_schema (1109)

解决办法： (禁用--column-statistics=0)

mysqldump --column-statistics=0 -h 127.0.0.1 -P 3306 -uroot -proot test > ${mysql_bak_dir}/test.sql
  • 问题3：
mysqldump: Got error: 2026: SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol when trying to connect

解决办法： (禁用--ssl-mode=DISABLED)

mysqldump --column-statistics=0 --ssl-mode=DISABLED -h 127.0.0.1 -P 3306 -uroot -proot test > ${mysql_bak_dir}/test.sql
  • 问题4：
mysqldump: Got error: 1044: Access denied for user 'root'@'%' to database 'test' when using LOCK TABLES

解决办法： (禁用--skip-lock-tables)

mysqldump --column-statistics=0 --ssl-mode=DISABLED --skip-lock-tables -h 127.0.0.1 -P 3306 -uroot -proot test > ${mysql_bak_dir}/test.sql
  • 问题5：
Error OS signal received(Internal error, programming error or missing error

解决办法：

DataMemory 过大导致，其大小*3(管理节点、数据节点、SQL节点)后不能超过free -h 中free的大小
  • 问题6：
集群在使用一段时间后无法创建表，增加字段或者增加索引

解决办法：

	创建集群时，在cluster.cnf中配置了相关的一些参数，如下：
 
	1、参数含义如下：
	MaxNoOfTables=20320	最大可创建表的数量
	MaxNoOfAttributes=100000	最大可创建属性数量
	MaxNoOfOrderedIndexes=3072	最大可创建索引数量
	2、可通过如下命令查看当前集群的参数配置，命令会返回当前所有节点配置的值
	ndb_config -q MaxNoOfTables
	ndb_config -q MaxNoOfAttributes
	ndb_config -q MaxNoOfOrderedIndexes
	3、修改配置：
	修改配置前请注意备份数据（SQL脚本或每个节点的mysql文件夹）；
	停止当前所有的管理节点；
	修改参数配置（具体参数值的大小可根据实际的环境配置和需求进行调整）；
	重新启动管理节点：ndb_mgmd -f /usr/local/mysql/mysql-cluster/cluster.cnf –initial
	管理节点启动后使用第二步的命令查看参数是否生效
	最后再执行mysql.sh重启其余的节点
Clone this wiki locally