mysql主从数据库搭建（系统均为windows）

参考从心出发的博客，网址：http://blog.sina.com.cn/s/blog_cfee55a70102wwa0.html

系统均为win7

1  确保2个主均可以自由通信，ping下测试

主：11.3.51.118
从：11.3.51.16

2  主服务器操作

在my.ini中最下面添加：

 
server-id=1 #指定唯一的ID，1至32，必须的
log-bin=mysql-log-bin #指定开启二进制日志存放路径，必须的

read-only              =0  #主机，读写都可以
binlog-do-db=clark #指定要同步的数据库，必须的

#binlog-ignore-db=mysql #指定不要同步的数据库，如果指定了binlog-do-db就不用再指定该项

*****************************************

binlog-do-db=test,xx,jj  ***********如果需要多个数据库的同步，请 分开写，否则认为这是一个数据库。

binlog_do_db=test

binlog_do_db=xx

binlog_do_db=jj

***********************************************************************

重启数据库服务

创建一个用户slave,赋予数据备份权限，只允许11.3.51.16
grant replication slave on *.* to slave@11.3.51.16 identified by '12345' ;

flush privileges;  //赋权限

显示主服务器的状态信息，并且找到File 和 Position 的值记录下来；
SHOW MASTER status;

mysql> SHOW MASTER status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| mysql-log-bin.000001 |       98 | clark        |                  |
+----------------------+----------+--------------+------------------+
1 row in set

3 从服务器配置

创建一个数据库 CREATE DATABASE clarkDEFAULT character set 'UTF8';

修改my.ini配置 (linux系统为my.cnf)

server-id=2 #指定唯一的ID，2至32，必须的，并且不能跟主数据库一样

replicate-do-db=clark #指定要同步的数据库，必须的

#replicate-ignore-db=mysql #指定不要同步的数据库

重启从数据库，设置登录主数据库的账号和密码等信息，然后启动slave

在mysql客户端操作如下：

CHANGE MASTER TO master_host = '11.3.51.118',

 master_user = 'slave',

 master_password = '12345',

master_port = 3307,

master_log_file ='mysql-log-bin.000001',#即前面记录的File值（或者直接设置位置：log_bin   = /var/log/mysql/mysql-bin.log --此种方式运行时有问题，  #确保此文件可写）

 master_log_pos = 98; #即前面记录的Position值

(或者：---这种方式不可行

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log  #或者直接设置位置：log_bin                 = /var/log/mysql/mysql-bin.log   #确保此文件可写
master-host     =11.3.51.118
master-user     =backup
master-pass     =123456
master-port     =3306
master-connect-retry=60 #如果从服务器发现主服务器断掉，重新连接的时间差(秒)
replicate-do-db =test #只复制某个库
replicate-ignore-db=mysql #不复制某个库

)

start SLAVE;

查看从数据库的信息

show slave status;

如果出现： Slave_IO_Running: YesSlave_SQL_Running:Yes以上两项都为Yes

如下：

+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+----------------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Slave_IO_State                   | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File          | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB            | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID                          | Master_Info_File           | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State                                | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+----------------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
| Waiting for master to send event | xx xxxxxx   | slave       |        3307 |            60 | mysql-bin.000001 |                 751 | ubuntu-relay-bin.000002 |           320 | mysql-bin.000001      | Yes              | Yes               | db1,db2,db3                |                    |                        |                         |                             |          0 |            |            0 |                 751 |             528 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |                             |                1 | 2c23d721-1df8-11e8-b371-005056a1267e | /var/lib/mysql/master.info |         0 |                NULL | Slave has read all relay log; waiting for more updates |              86400 |             |                         |                          |                |                    |                    |                   |             0 |                      |              |                    |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+----------------------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+----------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+
1 row in set (0.00 sec)

4 测试

在主数据库中的clark 中添加表和数据，从服务器即进行相应的同步。

注：如果信息同步中间出现问题，如 Slave_SQL_Running为NO，则此时需要重新启动slave，先查看show master status;之后停止从服务器stop slave;，添加新的

CHANGE MASTER TO master_host = '11.3.51.118',

 master_user = 'slave',

 master_password = '12345',

 master_log_file ='mysql-log-bin.000001',#即前面记录的File值

 master_log_pos = 1098;  #新的值，之后再次start slave即可。

5 如果半路进行主从复制

假设主数据库已有数据库clark_2，包含表school。

修改my.ini，增加

binlog-do-db=clark_2 #指定要同步的数据库，必须的,可添加多个

重启主数据库，然后在主数据库中锁定所有的表

flush tables with read lock;

显示主服务器的状态信息，并且找到File 和Position 的值记录下来；
SHOW MASTER status;

step2，从节点配置

将主数据库数据文件夹 data目录下clark_2与ibdata1拷贝到从数据库data目录下

编辑my.ini，增加

replicate-do-db=clark_2#指定要同步的数据库，可配置多个

重启从数据库

，其他操作，都没有问题后，开始主数据库解锁：

unlock tables;

6  linux下mysql数据存储位置

/var/lib/mysql

7 mysql修改用户密码

UPDATE user SET password=PASSWORD('123456') WHERE user='backup';
FLUSH PRIVILEGES;