MySQL master-slave replication and primary master replication

First, the preparation (master and slave need to configure):

Yum-y install MySQL mysql-server#安装mysql

Yum-y Install ntpdate#安装时间同步

Echo ' */1 * * * * */usr/sbin/ntpdate ntp1.aliyun.com &>/dev/null '>>/var/spool/cron/root #配置网络时间同步

Service mysqld start#启动服务

Chkconfig--add mysqld#添加为系统服务

Chkconfig mysqld on#开启默认运行级别

Second, the primary server configuration: The main configuration file by default:/ETC/MY.CNF

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

Symbolic-links=0

Log-bin=master-bin#开启二进制日志文件

Server-id=1#设置服务器id to ensure global uniqueness

Service mysqld Restart#重启动服务

mysqladmin-u root password 123.com#设置数据库管理员密码

Mysql-u root-p#登录数据库

Show global variables like '%server% '; #数据库id

Show master logs; #查看数据库二进制日志

Show master status; #查看主服务器状态, remember the binary log name, which is configured from the server

Grant replication Slave,replication Client on * * to ' copy ' @ ' 192.168.0.50 ' identified by ' 123.com ';

#授权192. Copy user on 168.0.50 host has replication permissions on all libraries

Select Host,user,password from Mysql.user; #查看用户

Third, from the server configuration:

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

Symbolic-links=0

Relay-log=slave-log#启用中继日志

server-id=10#服务器id, globally unique

Read-only=1#将服务器设置为只读模式 (invalid for a user with root privileges)

Service mysqld Restart#重启动服务

mysqladmin-u root password 123.com#设置数据库管理员密码

Mysql-u root-p#登录数据库

Change Master to master_host= ' 192.168.0.10 ', master_user= ' copy ', master_password= ' 123.com ', master_log_file= ' Master-bin.000001 ', master_log_pos=590;

Master_host: Primary server address

Master_user: A user with copy rights

Master_password: Password

Master_log_file: Which binary log to start copying from

Master_log_pos: Where to start copying from where the binary logs are

Start slave; #开启从服务器

show slave status\g; #查看从服务器状态

Iv. verification of Master and slave:

Primary server:

Mysql-u root-p

Create Database db_test; #创建数据库

From the server:

Mysql-u root-p

show databases; #查看服务器是否同步

Create Database mage; #SQL执行失败为从服务器配置只读成功

V. PRIMARY MASTER replication

Server A:

[Mysqld] #添加内容

Log-bin=master-bin#启用二进制日志

Relay-log=slave-log#启用中继日志

Server-id=1#服务器id, globally unique

Auto-increment-offset=1#自增列起始

auto-increment-increment=2#自增列一次步进 (example: 1,3,5,7,9 ...)

Mysql-uroot-p#进入数据库

Grant replication Slave,replication Client on * * to ' copy ' @ ' 192.168.0.50 ' identified by ' 123.com ';

Change Master to master_host= ' 192.168.0.50 ', master_user= ' copy ', master_password= ' 123.com ', master_log_file= ' Master-bin.000001 ', master_log_pos=106;

Start slave;

show slave status\g;

Server B:

[Mysqld] #添加内容

Log-bin=master-bin

Relay-log=slave-log

server-id=10

auto-increment-offset=2#自增列起始

auto-increment-increment=2#自增列一次步进 (example: 2,4,6,8,10 ...)

Mysql-uroot-p#进入数据库

Grant replication Slave,replication Client on * * to ' copy ' @ ' 192.168.0.10 ' identified by ' 123.com ';

Change Master to master_host= ' 192.168.0.10 ', master_user= ' copy ', master_password= ' 123.com ', master_log_file= ' Master-bin.000003 ', master_log_pos=106;

Start slave;

show slave status\g;

This article is from the "Automated Operations" blog, please be sure to keep this source http://hongchen99.blog.51cto.com/12534281/1917137

MySQL master-slave replication and primary master replication