mysql 5.6部署
1、清除遗留环境
rpm -qa|grep -i mysql
rpm -qa|grep mariadb
yum remove mariadb-libs -y
rm -rf /etc/my.cnf
2、清除安装目录
find / -name mysql | xargs rm -rf
修改hots
vi /etc/hosts
echo 10.0.0.19 19cDB >>/etc/hosts
3、解压安装包
tar zvxf mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.6.51-linux-glibc2.12-x86_64 /data/mysql
4、创建用户用户
cat /etc/group | grep mysql
cat /etc/passwd |grep mysql
groupadd mysql
useradd -r -g mysql mysql
5、目录权限
mkdir /data/mysql/log
mkdir /data/mysql/mydata
mkdir /data/mysql/tmp 这次不用
chown -R mysql:mysql /data/mysql
6、安装依赖包
yum -y install autoconf
yum install libaio* -y
7.设置环境变量
echo 'export PATH=$PATH:/data/mysql/bin' >>/etc/profile
source /etc/profile
mysql -V
8、配置启动脚本
在mysql路径下
cp support-files/mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
chkconfig --add mysqld
9、修改my.cnf
cp support-files/my-default.cnf /etc/my.cnf
[client]
#client character set
default_character_set = utf8
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
user = mysql
basedir=/data/mysql
datadir=/data/mysql/mydata
character_set_server = utf8
default_storage_engine = innodb
port=3306
max_connections = 1000
skip_name_resolve = on
pid-file = /data/mysql/log/mysql.pid
connect_timeout = 10
wait_timeout = 300
interactive_timeout = 300
lower_case_table_names = 1
log_bin_trust_function_creators=1
slow_query_log = on
slow_query_log_file = /data/mysql/log/mysql_slow.log
long_query_time=3
log_error = /data/mysql/log/mysql.err
expire_logs_days = 14
######innodb configure######
#innodb memory ,byte
innodb_buffer_pool_size=12G 50%~70%内存
#instances of innodb
innodb_buffer_pool_instances=8
#dump cache to disk before server shutdown
innodb_buffer_pool_dump_at_shutdown=on
#immediate dump buffer to disk
innodb_buffer_pool_dump_now=on
#load cache from disk when server start
innodb_buffer_pool_load_at_startup=on
#immediate load buffer_pool
innodb_buffer_pool_load_now=on
#log buffer size 8 to 32M
innodb_log_buffer_size=8M
#bin log size
innodb_log_file_size=256M
#write log buffer to disk,0,write every second;1,when transaction commit;2,transaction commit,every second,do not synchronization
innodb_flush_log_at_trx_commit=1
######MyIsam configure######
key_buffer_size=256M
read_buffer_size=256K
read_rnd_buffer_size=256K
sort_buffer_size=256K
join_buffer_size=256K
socket=/data/mysql/tmp/mysql.sock 这次部署是/tmp下
[mysql]
socket=/data/mysql/tmp/mysql.sock
10、socket文件
touch /data/mysql/tmp/mysql.sock
cd /data/mysql/tmp
chown mysql:mysql mysql.sock
chmod 755 /data/mysql/tmp/mysql.sock
11、初始化数据
./scripts/mysql_install_db --user=mysql --basedir=/data/mysql --datadir=/data/mysql/mydata
ln -s /data/mysql/tmp/mysql.sock /tmp/mysql.sock
12、启动数据库
systemctl start mysqld
13、修改mysqlroot密码
/data/mysql/bin/mysqladmin -u root password '123456'
14、删除匿名用户
mysql -uroot -p123456
use mysql
delete from user where user=''; 建议删除匿名用户
15、通过sql语句，将user表的 host 改为 %,否则外网通过客户端工具会链接不上
update user set host ='%' where user ='root' and host ='localhost';
16、刷新权限
flush privileges;
迁移和授权
1、查看用户和主机信息
select user,host from mysql.user
2、查看某个用户的权限
show grants for 用户名@'主机地址'
show grants for root@'%';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*E35AA65EE2858C12E6698DDA54C817C5CCA2613A'
3、导入数据
mysql -uroot -p123456 < mysqldump_20210702.sql
