mysql 双主加主从_mysql主从复制（添加从服务器）和双主复制

思路：

master:

1、启用二进制日志

2、定义 server-id

3、创建有复制权限的账户

完善设置

4、设置事务日志从内存缓冲直接同步存储到磁盘中

slave：

1、启用中继日志

2、定义server-id

3、使用有复制权限的帐号连接master

4、启动io thread以及sql thread

完善设置

5、定义节点为只读数据库

MariaDB复制操作(从零开始)

环境：

vm1-master:IP 172.16.3.2/16

vm2-slave: IP 172.16.3.10/16

1、mariadb安装(master节点配置)

# mkdir -pv /mydata/data

# tar xf mariadb-5.5.36-linux-x86_64.tar.gz -C /usr/local

# cd /usr/local

# ln -sv mariadb-5.5.36-linux-x86_64 mysql

# useradd -r mysql

# chown -R mysql.mysql /mydata/data/

# cd mysql/

# chown -R root.mysql ./*

# mkdir /etc/mysql

# cp support-files/my-large.cnf /etc/mysql/my.cnf

# vim /etc/mysql/my.cnf

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

datadir = /mydata/data   添加数据存储目录

sync_binlog=1           设置事务日志从内存缓冲直接同步存储到磁盘中

# cd /usr/local

# cp  support-files/mysql.server /etc/rc.d/init.d/mysqld

# chmod +x /etc/rc.d/init.d/mysqld

# chkconfig --add mysqld

# scripts/mysql_install_db --datadir=/mydata/data/ --user=mysql

# mkdir -pv /mydata/binlogs/

# chown -R mysql.mysql /mydata/binlogs

# vim /etc/mysql/my.cnf

log-bin         = /mydata/binlogs/master-bin     指定二进制的存放位置，和名称

binlog_format   = mixed                     开启 记录格式

server-id       = 1                            server-id 等于1

# service mysqld start

# vim /etc/profile.d/mysql.sh

export PATH=/usr/local/mysql/bin:$PATH

# source /etc/profile.d/mysql.sh

2、创建有复制权限的帐号

# mysql

MariaDB [(none)]> grant replication slave,replication client on *.* to ning@'172.16.3.10'identified by 'ning';

Query OK, 0 rows affected (0.04 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master status;                  查看二进制位置

+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master-bin.000001 |      486 |              |                  |

+-------------------+----------+--------------+------------------+

1 row in set (0.02 sec)

mariadb安装(从slave节点配置)

1、安装

# mkdir -pv /mydata/data

# tar xf mariadb-5.5.36-linux-x86_64.tar.gz -C /usr/local

# cd /usr/local

# ln -sv mariadb-5.5.36-linux-x86_64 mysql

# useradd -r mysql

# chown -R mysql.mysql /mydata/data/

# cd mysql/

# chown -R root.mysql ./*

# mkdir /etc/mysql

# cp support-files/my-large.cnf /etc/mysql/my.cnf

# vim /etc/mysql/my.cnf

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

datadir = /mydata/data   添加数据存储目录

read_only = 1  启动从节点是只读(不允许写入数据)

# cd /usr/local

# cp  support-files/mysql.server /etc/rc.d/init.d/mysqld

# chmod +x /etc/rc.d/init.d/mysqld

# chkconfig --add mysqld

# scripts/mysql_install_db --datadir=/mydata/data/ --user=mysql

# mkdir -pv /mydata/relaylogs/

# chown -R mysql.mysql /mydata/relaylogs

# vim /etc/mysql/my.cnf

#log-bin=mysql-bin                                  不记录二进制日志</