mysql主从

知识点

背景

解决问题

【读写分离】、【自动备份】、【负载均衡】
实现原理
1. mysql服务器之间的主从同步时基于二进制日志机制，主服务器使用二进制日志来记录数据库的变动情况，从服务器通过读取和执行该日志文件来保持和主服务器的数据一致
2. 在使用二进制日志时，主服务器的所有操作都会被记录下来，然后从服务器会接受到该日志的一个副本。从服务器可以执行执行该日志中的哪一类事件(譬如只插入数据或只更新数据)，默认会执行日志中的所有语句
3. 每一个服务器会记录关于二进制日志的信息：文件名和已经处理过的语句，这样意味着不同的从服务器可以执行同一个二进制文件的不同部分，并且从服务器可以随时连接或者中断和服务器的连接
4. 主服务器和每一个从服务器都必须配置一个唯一的ID号(在my.cnf文件的【mysqld】模块有一个server-id配置项)。此外每一个从服务器还需要通过change master to语句来配置它要连接的主服务器的IP地址、日志文件名称、该日志文件里面的位置(这些信息存储在主服务器的数据库中)
优点
1. 通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态的调整从服务器的数量，从而调整整个数据库的性能
2. 提高数据安全，因为数据已经复制到从服务器，从服务器可以终止复制进程，所以可以在从服务器上备份而不破坏主服务器相应的数据
3. 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能

主从服务器配置

将主服务器上的数据库全部备份

# 备份指定的数据库
mysqldump -u用户 -p密码 数据库名 > 备份的sql名.sql

# 还原数据库
# 方法1：进入指定的数据库，用source
source 备份.sql
# 方法2：重定向还原
mysql -u用户 -p密码 数据库名 < 备份.sql

# 备份所有的数据库
# --all-databases：表示所有的数据库
# --lock-all-tables；锁定所有的表，不允许备份期间修改数据
mysqldump -uroot -p密码 --all-databases --lock-all-tables > 备份.sql

在从服务器上还原主服务器上备份的数据库
1
mysqldump -uroot -p密码 < 备份.sql

配置主服务器中/etc/mysql/mysql.cnf.d/mysqld.cnf中的【log_bin】和【server_id】，如果有被注释则取消注释

# server_id：主服务器和从服务器的ID一定要不同，建议配置为各服务器自己的IP
sudo vim /etc/mysql/mysql.cnf.d/mysqld.cnf

# log_bin：二进制日志文件的路径
# server_id：主服务器的ID值

重启主服务器的mysql服务
1
sudo service mysql restart
在主服务器数据库创建一个用户，专门用户读取主数据库的二进制日志文件信息
1
2
grant replication slave on *.* to '用户名'@'192.168.0.1' identified by '密码';
flush privileges;

查看主服务器配置结果，同时也是获取主服务器上的二进制日志信息

1 2	# 以root用户登录数据后执行，获取其中的File、position show master status;

配置从服务器中/etc/mysql/mysql.cnf.d/mysqld.cnf中的【server_id】，注释掉【log_bin】内容

在从服务器配置主服务器的连接

# 以root用户登录数据库，
# master_user：用之前专门创建的读取二进制日志文件的账户
# master_log_file、master_log_pos：通过show master status查询到的信息File、Position
change master to master_host = '主服务器IP', master_user = '主服务器用户', master_password = '主服务器用户对应的密码', master_log_file = '二进制文件信息', master_log_pos=日志文件对应的值;

查看从服务器配置结果

# \G：为了便于输出的结果更容易阅读
show slave status \G;

# 查看数据结果中【Slave_IO_Running】、【Slave_SQL_Running】是否是【yes】，反之则表示主从关系未建立好