mysql双主搭建和垂直、水平拆分总结

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和分布式事务。