主（192.168.0.214）--从（192.168.0.119）复制

两台机器mysql版本一致。

配置主库/etc/my.cnf文件

[root@dgryxrdb mysql]# less /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/data

server-id = 1 --设置ID，服务器唯一ID，默认是1，一般取IP最后一段

log-bin = mysql-bin --启动二进制日志，slave会基于此log-bin来做replication

binlog_format = mixed --指定二进制日志的记录格式为混合模式（分为三种，statement：语句，row：行（事务），mixed：两者混合）

./bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking & --跳过授权启动，访问不需要读取授权表

$mysql -u root -p mysql

update user set password=password("root") where user="root";

--5.7版本设置如下：

mysql> update user set authentication_string=password("root") where user="root"; ---貌似不需要吧，这是干什么的……

Query OK, 0 rows affected, 1 warning (0.01 sec)

Rows matched: 1 Changed: 0 Warnings: 1

mysql> flush privileges; --重读授权表

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO reuser@'192.168.%.%' IDENTIFIED BY 'repass'; --创建具有复制权限的用户，一般不用root账户，%表示任意值，可指定具体的IP，加强安全

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status --查看二进制日志中事件的位置

-> ;

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

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

| mysql-bin.000007 | 154 | | | |

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

1 row in set (0.00 sec)

--执行完此步骤后，不要再操作主服务器的Mysql，防止主服务器状态值发生变化（或者主库增加读锁，等从库数据导入后再unlock tables）。

从库配置：

配置my.cnf文件

root@nfs mysql]# vi /etc/my.cnf

[mysqld]

datadir=/usr/local/mysql/data

server-id = 2

log-bin = mysql-bin

binlog_format = mixed

relay-log=/usr/local/mysql/data/relay-bin --指定中继日志的位置

read-only = 1 --开启只读模式

./bin/mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

$mysql -u root -p mysql

mysql> update user set authentication_string=password("root") where user="root";

Query OK, 0 rows affected, 1 warning (0.01 sec)

Rows matched: 1 Changed: 0 Warnings: 1

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> use mysql

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement

mysql> set PASSWORD=PASSWORD('root'); --貌似大小写敏感

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO reuser@'192.168.%.%' IDENTIFIED BY 'repass';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status; --查看从服务器的状态信息

mysql> show processlist; --查看启动线程

结果显示没启动任何线程

配置从服务器连接主服务器

mysql> help change master to //查看命令

--配置从服务器连接主服务器

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.214', MASTER_USER='reuser', MASTER_PASSWORD='repass', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=154;

Query OK, 0 rows affected, 1 warning (0.01 sec)

--启动线程，从服务器复制功能

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

--查看服务器工作状态

mysql> show slave status\G;

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

Slave_IO_State: Waiting for master to send event --IO状态

Master_Host: 192.168.0.214 --主服务器IP

Master_User: reuser --具有复制权限的用户

Master_Port: 3306 --默认监听的端口

Connect_Retry: 60 --重试时间

Master_Log_File: mysql-bin.000007 --读取的二进制文件

Read_Master_Log_Pos: 154 --二进制日志中的位置，》=exec_master_log_pos

Relay_Log_File: relay-bin.000002 --当前读取的中继日志文件

Relay_Log_Pos: 320 --中继日志中事件的位置

Relay_Master_Log_File: mysql-bin.000007

Slave_IO_Running: Yes --IO线程已启动，必须为yes

Slave_SQL_Running: Yes --SQL线程已启动，必须为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: 154

Relay_Log_Space: 521

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:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 30b5f2ea-e4c1-11e4-b0ef-000c294fd284

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

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

1 row in set (0.00 sec)

ERROR:

No query specified

主库创建数据库

mysql> create database mydb;

Query OK, 1 row affected (0.04 sec)

mysql> show databases;

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

| Database |

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

| information_schema |

| lqq_test |

| mydb |

| mysql |

| performance_schema |

| sys |

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

6 rows in set (0.00 sec)

查看从库：

mysql> show databases;

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

| Database |

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

| information_schema |

| mydb |

| mysql |

| performance_schema |

| sys |

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

主-从之间的最初数据同步（数据初始化）

--主库锁表，防止再操作

mysql> flush tables with read lock;

Query OK, 0 rows affected (0.01 sec)

mysql> show master status;

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

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

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

1 row in set (0.00 sec)

进行数据备份

[mysql@leonfs data]$mysqldump -uroot -proot -P3306 -h 192.168.0.53 --all-databases >mysql53.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.[mysql@leonfs ~]$scp mysql53.sql mysql@192.168.0.119:/home/mysql

The authenticity of host '192.168.0.119 (192.168.0.119)' can't be established.

RSA key fingerprint is 3c:13:c9:11:d3:99:c1:c8:7c:e0:53:4c:73:d7:10:63.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.0.119' (RSA) to the list of known hosts.

mysql@192.168.0.119's password:

mysql53.sql

--从库导入备份

mysql> source /home/mysql/mysql53.sql

Mysql 主-从简单配置---centos6.4