mysql的主从复制已经搭建并实践过了,现在搭建双主互备,最终效果是,任何一个数据库有写操作,另外一台都可以复制。
以下是基于mysql5.7版本安装:
1.安装并配置数据库;
1.1 解压安装包;
1.2 新建文件夹data
1.3 新建文件my.ini
修改配置如下,其中主从配置为红色字体:
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysql]
default-character-set=utf8mb4
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
basedir = D:\\IDE\\mysql\\mysql-dh1
datadir = D:\\IDE\\mysql\\mysql-dh1\\data
character_set_server=utf8mb4
server-id=1271
log-bin=mysql-bin # 开启二进制日志
log-slave-updates # 在作为从数据库的时候,有写入操作也要更新二进制日志文件,如果主库不作为从库就不要配置
# 不同步哪些数据库
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
# 只同步哪些数据库,除此之外,其他不同步
# binlog-do-db = game
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
max_allowed_packet = 20M
max_connections = 1000
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
lower_case_table_names = 1
2.数据库初始化和启动
2.1 初始化并记录窗口显示的初始化密码
mysqld --initialize --user=mysql --console
2.2 将数据库设置成服务并启动
mysqld --install mysql
net start mysql
2.3 重置初始化密码
mysql -u root -P3306 -p
mysql> set password for root@localhost=password('root');
3.设置从库从主库复制
3.1 登录主库,查看主库状态,并记录File\Position;
show master status;
3.2 登录从库,关闭从库复制状态;
stop slave;
3.3 设置从库复制主库功能,其中file\pos就是主库的File\Position
CHANGE MASTER TO master_host = '127.0.0.1',
MASTER_PORT = 3308,
master_user = 'root',
master_password = 'root',
master_log_file = 'mysql-bin.000001',
master_log_pos = 154;
3.4启动主从复制功能
start slave;
3.5 查看从库复制状态
show slave status;
其中Slave_IO_Running\Slave_SQL_Running都为yes即正常
3.6 按照3.1-3.5步骤将从库当初主库,主库当初从库配置一次即可
4 总结
mysql 的主从是基础,主从互相复制只不过是一个数据库即当主又当从;一个主可以对应n个从,从而减少mysql的读压力,但过多的主从复制,数据库的写的压力也会增加。
5.分布式数据库
分布式数据库目前主要是靠中间件来实现的,例如mycat。核心的功能都是对原有的一个数据库进行垂直、水平拆分,从而解决单个数据库、单张表的压力,但也增加了节点分片join难度,所有最优的方式是利用表分组思想,将业务表水平拆分后,根据业务规则,有关联的表数据都存放到相同的节点分片,从而规避分片join和分布式事务。