linux中mysql主从安装图片_linux中mysql主从配置步骤详解

一、mysql主从方式:

同步:主服务器有数据写入，存储在硬盘，记录二进制日志一份。二进制日志完整发送给从服务器记录于中继日志中，从服务器回应主服务器已收到，

异步:主服务器写入数据，存储磁盘，记录二进制日志，直接反馈客户端；至于从服务器收到与否，不予理会

mysql的复制默认为异步模式

二、配置主从服务器

1.先在两台机器上分别安装好mysql，版本需要一致，然后配置，启动

2.主服务器配置

1).查看mysql是否正常启动

netstat -nltp | grep 3306

2)修改配置文件，开启二进制日志

2.1.vim /etc/my.cnf

将

log-bin=mysql-bin改为log-bin=/usr/local/mysql/var/binlogs/master-bin

保存

2.2.创建二进制日志目录

mkdir -p /usr/local/mysql/var/binlogs/

2.3.对目录权限进行授权

chown mysql.mysql /usr/local/mysql/var/binlogs/

2.4.重启mysql服务

service mysqld restart

3)创建有复制权限的账号

mysql>grant replication slave,replication client on *.* to 'replication'@'192.168.5.10' identified by 'Looking_s123';

mysql>flush privileges;

3.从服务器配置

1)查看mysql是否正常启动

netstat -ntlp | grep 3306

2)修改配置文件

vim /etc/my.cnf

将二进制日志文件注释

log-bin=mysql-bin

binlog_format=mixed

修改server-id

将

server-id =1

修改为

server-id =2

开启中继日志

relay-log =/usr/local/mysql/var/relaylogs/relaylogs

创建中继日志目录及授权

mkdir -p /usr/local/mysql/var/relaylogs/

chown -R mysql.mysql /usr/local/mysql/var/relaylogs/

3)重启服务，确保中继日志启动

service mysqld restart

4)查看中继日志启动状态

mysql> show global variables like '%relay%';

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

| Variable_name | Value |

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

| max_relay_log_size | 0 |

| relay_log | /usr/local/mysql/var/relaylogs/relaylogs |

| relay_log_index | |

| relay_log_info_file | relay-log.info |

| relay_log_purge | ON |

| relay_log_space_limit | 0 |

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

6 rows in set (0.00 sec)

5)连接主服务器

mysql>change master to master_host='192.168.5.11',master_user='replication',master_password='Looking_s123';

6)手动启动复制线程

mysql>start slave;

7)查看主从状态

mysql> show slave status \G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.5.11

Master_User: replication

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000003

Read_Master_Log_Pos: 317

Relay_Log_File: relaylogs.000005

Relay_Log_Pos: 463

Relay_Master_Log_File: master-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 317

Relay_Log_Space: 999

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

ERROR:

No query specified

三.测试

在主服务器上创建数据库和表，在从服务器上查看复制效果，如果同步了，就说明配置成功了！