linux mysql主从同步_Linux环境下mysql主从同步环境搭建

#my.cnf添加内容

vim /etc/my.cnf

添加以内容：

## replication

server_id=195

binlog-ignore-db=mysql

binlog_format=mixed

log-bin=master-mysql-bin

#登录数据服务器，输入密码

mysql -h 主ip -u root -p

use mysql;

#新建同步账号(从数据库服务器IP与用户以及密码)

grant replication slave, replication client on *.* to 'root'@'从ip' identified by 'Nsk123';

#账号权限生效

flush privileges;

#master数据服务器状态

show master status

显示如下信息：

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| master-mysql-bin.000002 | 120 | | mysql | |

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

#从服务器安装好之后查看主服务器

show processlist\G;

如果显示以下信息则说明安装成功：

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

Id: 2

User: root

Host: 从ip:32902

db: NULL

Command: Binlog Dump

Time: 37

State: Master has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

*************************** 2. row ***************************

#在主数据库服务器上新建一个表，看下是不是会立即同步到从数据库对应数据库里面

#从服务器设置

#my.cnf添加内容

vim /etc/my.cnf

添加以内容：

## replication

server-id =194

relay-log=relay-bin

relay-log-index=relay-bin

replicate-ignore-db=mysql

replicate-ignore-db=information_schema

#链接从数据库服务器,输入密码

mysql -h 从ip -u root -p

use mysql

#停止从数据库服务器

stop slave;

#添加主数据库服务器相关信息(主IP,用户名，密码，主binlog文件名称，这个与show master status命令显示的File一致，,MASTER_LOG_POS是主日记文件的行数，show master status#命令显示的Position一致)

CHANGE MASTER TO MASTER_HOST='主ip',MASTER_USER='root',MASTER_PASSWORD='Nsk123',MASTER_LOG_FILE='master-mysql-bin.000002',MASTER_LOG_POS=120;

#显示从数据库服务器状态

show slave status\G;

显示如下信息：

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

Slave_IO_State:

Master_Host: 主ip

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-mysql-bin.000002

Read_Master_Log_Pos: 120

Relay_Log_File: relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: master-mysql-bin.000002

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,information_schema

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: 120

Relay_Log_Space: 120

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: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 1236

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 195

Master_UUID: 7ed5adb7-0928-11d6-b93f-ec388f720bcb

Master_Info_File: /usr/local/dev/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp: 181215 21:27:17

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

#启动从数据库服务器

start slave;