Linux学习（5）-mysql主从备份（热备份）配置及验证实例

1.mysql启动、停止、重启

service mysqld start

service mysqld stop

service mysqld restart

2.准备几个文件，没有的自己创建到linux目录下

主机数据库配置文件  /etc/my.cnf

[mysqld]
## 常规配置
character-set-server = utf8
collation-server = utf8_general_ci
max_connections = 2000

port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M

# 调整为机器内存的 50%
innodb_buffer_pool_size = 2G

innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

## 主从配置
log-bin=mysql-bin
binlog_format=mixed

# 需要在一套主从同步的 MySQL 服务器之间唯一
server-id = 1

从机数据库配置文件  /etc/my.cnf

[mysqld]
## 常规配置
character-set-server = utf8
collation-server = utf8_general_ci
max_connections = 2000

port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M

# 调整为机器内存的 50%
innodb_buffer_pool_size = 1G

innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

## 主从配置
log-bin=mysql-bin
binlog_format=mixed

# 需要在一套主从同步的 MySQL 服务器之间唯一
server-id = 2

主机设置数据库用户及修改密码sql语句  master.sql

ALTER USER 'root'@'localhost' IDENTIFIED BY 'TSF-qcloud1';
GRANT ALL PRIVILEGES ON *.* to root@'localhost' IDENTIFIED BY 'TSF-qcloud1' WITH GRANT OPTION;
FLUSH PRIVILEGES;

CREATE USER 'root'@'%' IDENTIFIED by 'TSF-qcloud1';
GRANT ALL PRIVILEGES ON *.* to root@'%' IDENTIFIED BY 'TSF-qcloud1' WITH GRANT OPTION;
FLUSH PRIVILEGES;

CREATE USER 'replicator'@'%' IDENTIFIED BY 'TSF-qcloud1';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

SHOW MASTER STATUS;

从机设置数据库用户及修改密码sql语句 slave.sql

ALTER USER 'root'@'localhost' IDENTIFIED BY 'TSF-qcloud1';
GRANT ALL PRIVILEGES ON *.* to root@'localhost' IDENTIFIED BY 'TSF-qcloud1' WITH GRANT OPTION;
FLUSH PRIVILEGES;

CREATE USER 'root'@'%' IDENTIFIED by 'TSF-qcloud1';
GRANT ALL PRIVILEGES ON *.* to root@'%' IDENTIFIED BY 'TSF-qcloud1' WITH GRANT OPTION;
FLUSH PRIVILEGES;

3.设置主机

mysql -uroot -p
# 输入密码
mysql> source master.sql;

将这里的  File ,  Position 的值纪录下来，在配置从机时需要用到。如：

 使 MySQL 开机自动运行。

systemctl enable mysqld

查询用户

select host,user from user

4.设置从机

mysql -uroot -p
# 输入密码
mysql> source slave.sql;

 查看用户

select host,user from user

下面开始加入主机，将  <master-host> 替换成主机的内网 IP， <master-log-file> 和  <master-log-pos> 替换为
master 运行  source master.sql 时得到的  File 和  Position 

CHANGE MASTER TO MASTER_HOST='<master-host>', MASTER_USER='replicator',MASTER_PASSWORD='TSF-qcloud1', MASTER_LOG_FILE='<master-log-file>', MASTER_LOG_POS=<master-log-pos>;
START SLAVE;
SHOW SLAVE STATUS\G

观察 Slave_SQL_Running_State 的值，如果是 "Slave has read all relay log; waiting for more updates"，即表示正常。

使 MySQL 开机自动运行。

 systemctl enable mysqld 

5.Mysq主从同步验证-主机从从机同步数据（没有支持）

在从机上执行

mysql> CREATE DATABASE tsf_test;

6.Mysq主从同步验证-从机自动与主机同步数据（支持）

在主机上执行

mysql> CREATE DATABASE tsf_test0;