1.卸载操作系统原有的mysql lib包和配置文件
sudo yum remove mysql-libs && sudo rm -rf /var/lib/mysql && sudo rm /etc/my.cnf && sudo rm -rf /etc/mysql
2.下载安装包
配置文件my.cnf,根据实际安装目录和需求修改配置
[mysqld]
basedir=/u01/deploy/mysql8.0.22
datadir=/u01/deploy/mysql/data
port=3306
server-id=200
log-bin=/u01/deploy/mysql/log/mysql-binlog
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
default-time_zone='+8:00'
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
default_authentication_plugin=mysql_native_password
max_connections=2000
max_connect_errors=100000
# (adjust value here, 50%-70% of total RAM)
innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=4
innodb_log_file_size=512M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=0
max_binlog_size=134217728
binlog_cache_size=8388608
innodb_flush_log_at_trx_commit = 0
innodb_autoinc_lock_mode=2
bulk_insert_buffer_size=128M
log_error = /u01/deploy/mysql/error.log
slow_query_log_file=/u01/deploy/mysql/slow.log
long_query_time=10
slow_query_log = 1
[mysql]
default-character-set=utf8mb4
[client]
default-character-set=utf8mb4
3.安装
解压
tar -zxvf mysql-8.0.22-el7-x86_64.tar.gz && mv mysql-8.0.22-el7-x86_64 mysql8.0.22
创建目录
mkdir -p /u01/deploy/mysql/data && mkdir -p /u01/deploy/mysql/log #log目录必须有 没有会报错
#上传配置文件
修改主从的my.cnf文件中的basedir、datadir、log-bin、log_error和server-id,确保主从server-id不一样
cd /etc && 上传my.cnf文件
sudo chmod 644 /etc/my.cnf
初始化数据库
/u01/deploy/mysql8.0.22/bin/mysqld --initialize --user=soaadmin --basedir=/u01/deploy/mysql8.0.22 --datadir=/u01/deploy/mysql/data
配置环境变量
sudo vi /etc/profile
export MYSQL_HOME=/u01/deploy/mysql8.0.22
export PATH=$MYSQL_HOME/bin:$PATH
source /etc/profile
复制服务文件
sudo cp -a /u01/deploy/mysql8.0.22/support-files/mysql.server /etc/init.d/mysqld
service mysqld start
service mysqld restart
sudo chkconfig --level 35 mysqld on
开机启动
vi /etc/rc.local 添加
su soaadmin -c "service mysqld start"
4.登录修改mysql root密码
查看初始安装密码
grep 'temporary password' /u01/deploy/mysql/error.log
mysql -uroot -p
#权限设置
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'xxxxx';
flush privileges;
5.主从复制配置
主库
CREATE USER 'backup'@'从库IP' IDENTIFIED WITH mysql_native_password BY 'backupxxxxxxx';
GRANT REPLICATION SLAVE ON *.* TO 'backup'@'从库IP';
flush privileges;
SHOW MASTER STATUS;
从库
change master to master_host='主库IP',master_user='backup',master_password='backupxxxxxxx',master_log_file='mysql-binlog.000004',master_log_pos=854;
master_log_file='mysql-binlog.000004'是show master status输出的mysql binlog日志编号
master_log_pos=854是show master status输出的log position
启动主从
start slave;
查看主从状态
show slave status\G
状态正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6.创建数据库及用户
CREATE DATABASE codec DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'codec'@'%' IDENTIFIED WITH mysql_native_password BY 'xxxxxxx';
grant all on codec.* to `codec`@`%`;
flush privileges;
show grants for codec;