1.下载 tar.gz 格式的安装包
下载地址 https://dev.mysql.com/downloads/mysql/
2.解压
tar -zvxf mysql-5.7.19-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.19-linux-glibc2.12-x86_64 mysql5719
mv mysql5719 /mysql
chown -R mysql:mysql /mysql/mysql5719
3.配置 my.cnf
master 节点配置,进入到/mysql/mysql5719 目录下面,创建文件 my.cnf,文件内容如下
[mysqld]
datadir=/mysql/mysql5719/data
socket=/mysql/mysql5719/data/mysql.sock
tmpdir = /tmp
user=mysql
basedir = /mysql/mysql5719
log-bin = master-bin
log-bin-index = master-bin.index
server-id = 1
# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0
#key_buffer = 16M
key_buffer_size = 32M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 64
query_cache_limit = 8M
query_cache_size = 64M
query_cache_type = 1
# Important: see Distributing the Databases and Setting max_connections
max_connections = 3000
binlog_format=mixed
log_bin_trust_function_creators = 1
## @!!@
lower_case_table_names = 1
#default-storage-engine=InnoDB
#character_set_client=utf8
#default-collation=utf8_unicode_ci
character-set-server=utf8
collation-server=utf8_unicode_ci
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
lower_case_table_names = 1
# InnoDB settings
innodb_data_home_dir=/mysql/mysql5719/data
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_buffer_pool_size = 2G
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_log_file_size = 512M
#skip-grant-tables
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
slave 节点配置,进入到/mysql/mysql5719 目录下面,创建文件 my.cnf,文件内容如下
[mysqld]
datadir=/mysql/mysql5719/data
socket=/mysql/mysql5719/data/mysql.sock
user=mysql
port = 3306
basedir =/mysql/mysql5719
tmpdir =/tmp
relay-log = slave-relay-bin
relay-log-index = slave-relay-bin.index
server-id = 2
# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0
#key_buffer = 16M
key_buffer_size = 32M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 64
query_cache_limit = 8M
query_cache_size = 64M
query_cache_type = 1
# Important: see Distributing the Databases and Setting max_connections
max_connections = 3000
binlog_format=mixed
log_bin_trust_function_creators = 1
## @!!@
lower_case_table_names = 1
#default-storage-engine=InnoDB
#character_set_client=utf8
#default-collation=utf8_unicode_ci
character-set-server=utf8
collation-server=utf8_unicode_ci
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
lower_case_table_names = 1
# InnoDB settings
innodb_data_home_dir=/mysql/mysql5719/data
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_buffer_pool_size = 2G
innodb_thread_concurrency = 8
innodb_flush_method = O_DIRECT
innodb_log_file_size = 512M
#skip-grant-tables
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
4.创建数据目录
mkdir /mysql/mysql5719/data
5.修改 mysql 服务启动脚本
6.初始化数据库(root 操作)
./bin/mysqld --defaults-file=./my.cnf --initialize
上图红框里面就是 root 的初始密码
7.启动 MySQL 服务(root 操作)
8.设置 mysql.sock
ln -s /mysql/mysql5721/data/mysql.sock /tmp/mysql.sock
9.设置 root 密码(初始密码在初始化数据库的时候生成)
set password for root@localhost = password('Zaq1@wsx');
10.设置 MySQL 服务
cp mysql.server /etc/init.d/mysqld
chmod 755 /etc/init.d/mysqld
如果出现下面的报错
原因:MySQL 默认的日志:/var/log/mysqld.log 被占用了,删除即可。
11.设置 MySQL 客户端
mv /usr/bin/mysql /usr/bin/mysql.bak #删除备份旧的mysql命令,若不存在可忽略
cp mysql /usr/bin/
chmod 755 /usr/bin/mysql
到此为止,master 机器的安装配置完成,接下来安装 slave 机器,slave 机器的安装和 master 类似,但请注意 my.cnf 配置不一样
12.在 master 上添加主从同步用户
GRANT REPLICATION SLAVE ON *.* TO 'rep_user'@'%' IDENTIFIED BY 'Qwer1@#$';
查看主服务器,出现以下类似信息
mysql> show master status;
13.配置 slave 服务器
mysql -u root -p #进入MySQL控制台
stop slave; #停止slave同步进程
change master to master_host='134.176.72.82',master_user='rep_user',master_password='Qwer1@#$',master_log_file='master-bin.000002' ,master_log_pos=837; #执行同步语句
start slave; #开启slave同步进程
SHOW SLAVE STATUS\G #查看slave同步信息,出现以下内容
注意查看:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
以上这两个参数的值为 Yes,即说明配置成功!
14.测试 MySQL 主从服务器双机热备是否成功
(1)进入 MySQL 主服务器
mysql -u root -p #进入主服务器MySQL控制台
mysql> create database test;
mysql> use test; #进入数据库
mysql> CREATE TABLE test ( id int not null primary key,name char(20) ); #创建test表
mysql> insert into test values(001,'zhangsan');
mysql> insert into test values(002,'lisi');
(2)进入 MySQL 从服务器
mysql -u root -p #进入MySQL控制台
use test; #进入数据库
show tables; #查看mstest库,会看到有一个新建的表test,表示数据库同步成功
select * from test;
欢迎来到这里!
我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。
注册 关于