mysql主从安装

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;