mysql主从同步与读写分离

主从同步：
一、配置主数据库
1、安装数据库并启动、设置为自动启动
[root@x4d000005 /]# yum install -y mariadb mariadb-server python2-PyMySQL -y
[root@x4d000005 /]# systemctl start mariadb.service
[root@x4d000005 /]# systemctl enable mariadb.service
2、初始化
[root@x4d000005 /]# mysql_secure_installation
回车输入密码后一路yes
3、修改主数据库配置文件
[root@x4d000005 /]# vi /etc/my.cnf
[mysqld]
max_connection=1000
binlog-ignore-db=mysql #新增
binlog-ignore-db=information_schema #新增
log-bin=mysql-bin
server-id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

include all files from the config directory

!includedir /etc/my.cnf.d
4、重启
[root@x4d000005 /]# systemctl restart mariadb.service
5、进入数据库进行权限修改
[root@x4d000005 /]# mysql -uroot -p123456
MariaDB [(none)]> show master status \G; 查看主数据库状态（记住日志文件名称和id号）

MariaDB [(none)]> GRANT REPLICATION SLAVE ON . to ‘slave’@’%’ identified by ‘123456’;授权
MariaDB [(none)]> GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘123456’ WITH GRANT OPTION; 授权
MariaDB [(none)]> flush privileges; 刷新

6、测试连接成功

二、配置从数据库
1、安装数据库
[root@x4d000006 /]# yum install -y mariadb mariadb-server python2-PyMySQL -y
[root@x4d000006 /]#systemctl restart mariadb.service
[root@x4d000006 /]# systemctl enable mariadb.service
[root@x4d000006 /]# mysql_secure_installation 初始化
2、编辑配置文件
[root@x4d000005 /]# vi /etc/my.cnf
[mysqld]
max_connections=100
server-id=2
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
relay-log=realy-bin
relay-log-index=slave-relay-bin.index
user=mysql

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

Settings user and group are ignored when systemd is used.

If you need to run mysqld under a different user or group,

customize your systemd unit file for mariadb according to the

instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

include all files from the config directory

!includedir /etc/my.cnf.d
3、重启
[root@x4d000006 /]# systemctl restart mariadb.service
4、设置主从数据库同步点
[root@x4d000006 /]# mysql -uroot -p123456
MariaDB [(none)]> change master to master_host=‘77.0.0.5’,master_password=‘123456’,master_user=‘root’,master_log_file=‘mysql-bin.000001’,master_log_pos=1240;
MariaDB [(none)]> start slave; 开启从服务
MariaDB [(none)]> show slave status \G;查看从数据库状态

5、授权
MariaDB [(none)]> GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘123456’ WITH GRANT OPTION;
MariaDB [(none)]> flush privileges;
6、测试
在主数据库创建enen数据库
MariaDB [(none)]> create database xiao;
从服务器同时创建，成功

读写分离：
原理：其实就是将数据库分为了主从库，一个主库用于写数据，多个从库完成读数据的操作，主从库之间通过某种机制进行数据的同步，是一种常见的数据库架构。
一、配置从数据库
1、安装插件，拖入软件包
[root@x4d000006 /]# yum install -y lrzsz

2、解压
[root@x4d000006 /]# yum install -y unzip
[root@x4d000006 /]# unzip amoeba-mysql-3.0.5-RC-distribution.zip
3、根据自己的习惯移动文件，进入
[root@x4d000006 /]# mv amoeba-mysql-3.0.5-RC /usr/local/amoeba
[root@x4d000006 /]# cd /usr/local/amoeba/
4、修改配置文件（三个）
[root@x4d000006 amoeba]# cd conf/
[root@x4d000006 conf]# vi dbServers.xml

[root@x4d000006 conf]# vi amoeba.xml

去掉注释，ip为本机ip


[root@x4d000006 amoeba]# vi jvm.properties