windows---------mysql的主从配置

一、环境

操作系统：windows；

主数据库：127.0.0.1:3306；

从数据库：127.0.0.1:3307；

需要主从同步的数据库：test；

二、数据库准备

主数据库：我采用的是phpstudy集成环境的mysql；端口是3306；

从数据库：我采用的是mysql5.6.11，下载地址：https://downloads.mysql.com/archives/community/   -----下载mysql5.6.11

三、数据库安装

主数据库：phpstudy一键安装；我的mysql安装地址是D:\phpstudy\mysql；（创建用户、修改密码就不多说了）

从数据库：将下载下来的mysql5.6.11安装包解压到D:\    ；mysql地址是D:\mysql5.6.11；（创建用户、修改密码就不多说了）

在目录D:\mysql5.6.11下面新建文件my.ini，并将下列代码复制进去：

注意：红色字体部分根据你的地址进行修改。

[client]  
port=3307  
default-character-set=utf8  
[mysqld]  
port=3307   
character_set_server=utf8  
basedir=D:\mysql5.6.11  


#解压目录  
datadir=D:\mysql5.6.11\data  
#解压目录下data目录  
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  
[WinMySQLAdmin]  
D:\mysql5.6.11\bin\mysqld.exe    
# The maximum amount of concurrent sessions the MySQL server will  
# allow. One of these connections will be reserved for a user with  
# SUPER privileges to allow the administrator to login even if the  
# connection limit has been reached.  
max_connections=100  
  
# Query cache is used to cache SELECT results and later return them  
# without actual executing the same query once again. Having the query  
# cache enabled may result in significant speed improvements, if your  
# have a lot of identical queries and rarely changing tables. See the  
# "Qcache_lowmem_prunes" status variable to check if the current value  
# is high enough for your load.  
# Note: In case your tables change very often or if your queries are  
# textually different every time, the query cache may result in a  
# slowdown instead of a performance improvement.  
query_cache_size=0  
  
# The number of open tables for all threads. Increasing this value  
# increases the number of file descriptors that mysqld requires.  
# Therefore you have to make sure to set the amount of open files  
# allowed to at least 4096 in the variable "open-files-limit" in  
# section [mysqld_safe]  
table_cache=256  
  
# Maximum size for internal (in-memory) temporary tables. If a table  
# grows larger than this value, it is automatically converted to disk  
# based table This limitation is for a single table. There can be many  
# of them.  
tmp_table_size=34M  
  
  
# How many threads we should keep in a cache for reuse. When a client  
# disconnects, the client's threads are put in the cache if there aren't  
# more than thread_cache_size threads from before.  This greatly reduces  
# the amount of thread creations needed if you have a lot of new  
# connections. (Normally this doesn't give a notable performance  
# improvement if you have a good thread implementation.)  
thread_cache_size=8  
  
#*** MyISAM Specific options  
  
# The maximum size of the temporary file MySQL is allowed to use while  
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.  
# If the file-size would be bigger than this, the index will be created  
# through the key cache (which is slower).  
myisam_max_sort_file_size=100G  
  
# If the temporary file used for fast index creation would be bigger  
# than using the key cache by the amount specified here, then prefer the  
# key cache method.  This is mainly used to force long character keys in  
# large tables to use the slower key cache method to create the index.  
myisam_sort_buffer_size=67M  
  
# Size of the Key Buffer, used to cache index blocks for MyISAM tables.  
# Do not set it larger than 30% of your available memory, as some memory  
# is also required by the OS to cache rows. Even if you're not using  
# MyISAM tables, you should still set it to 8-64M as it will also be  
# used for internal temporary disk tables.  
key_buffer_size=54M  
  
# Size of the buffer used for doing full table scans of MyISAM tables.  
# Allocated per thread, if a full scan is needed.  
read_buffer_size=64K  
read_rnd_buffer_size = 2M   
  
# This buffer is allocated when MySQL needs to rebuild the index in  
# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE  
# into an empty table. It is allocated per thread so be careful with  
# large settings.  
sort_buffer_size = 2M  
  
  
# Remove leading # to set options mainly useful for reporting servers.  
# The server defaults are faster for transactions and fast SELECTs.  
# Adjust sizes as needed, experiment to find the optimal values.  
  join_buffer_size = 128M  
  
#*** INNODB Specific options ***  
  
  
# Use this option if you have a MySQL server with InnoDB support enabled  
# but you do not plan to use it. This will save memory and disk space  
# and speed up some things.  
#skip-innodb  
  
# Additional memory pool that is used by InnoDB to store metadata  
# information.  If InnoDB requires more memory for this purpose it will  
# start to allocate it from the OS.  As this is fast enough on most  
# recent operating systems, you normally do not need to change this  
# value. SHOW INNODB STATUS will display the current amount used.  
innodb_additional_mem_pool_size=3M  
  
# If set to 1, InnoDB will flush (fsync) the transaction logs to the  
# disk at each commit, which offers full ACID behavior. If you are  
# willing to compromise this safety, and you are running small  
# transactions, you may set this to 0 or 2 to reduce disk I/O to the  
# logs. Value 0 means that the log is only written to the log file and  
# the log file flushed to disk approximately once per second. Value 2  
# means the log is written to the log file at each commit, but the log  
# file is only flushed to disk approximately once per second.  
innodb_flush_log_at_trx_commit=1  
  
# The size of the buffer InnoDB uses for buffering log data. As soon as  
# it is full, InnoDB will have to flush it to disk. As it is flushed  
# once per second anyway, it does not make sense to have it very large  
# (even with long transactions).  
innodb_log_buffer_size=2M  
  
# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and  
# row data. The bigger you set this the less disk I/O is needed to  
# access data in tables. On a dedicated database server you may set this  
# parameter up to 80% of the machine physical memory size. Do not set it  
# too large, though, because competition of the physical memory may  
# cause paging in the operating system.  Note that on 32bit systems you  
# might be limited to 2-3.5G of user level memory per process, so do not  
# set it too high.  
innodb_buffer_pool_size=104M  
  
# Size of each log file in a log group. You should set the combined size  
# of log files to about 25%-100% of your buffer pool size to avoid  
# unneeded buffer pool flush activity on log file overwrite. However,  
# note that a larger logfile size will increase the time needed for the  
# recovery process.  
innodb_log_file_size=52M  
  
# Number of threads allowed inside the InnoDB kernel. The optimal value  
# depends highly on the application, hardware as well as the OS  
# scheduler properties. A too high value may lead to thread thrashing.  
innodb_thread_concurrency=10  

四、修改数据库配置

1、修改配置文件

主数据库：在【mysqld】中添加如下代码

从数据库：在【mysqld】中添加如下代码

2、在主数据库创建用户‘repl’并指定replication权限

create user 'repl'@'127.0.0.1' identified by '123456';   -------identified by  对密码进行加密处理

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1';    ------- ‘ *.* ’全库

3、确认主、从mysql的test数据库初始状态一致

你可以选择copy磁盘上的数据库文件夹。我这里直接停止服务，然后将数据文件拷贝过去；

4、查看主数据库master的值

在主数据库里面运行show master status;得到file和position字段对应的参数。

mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | test | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

5、设置从数据库master的值

mysql> change master to

->master_host='127.0.0.1',

->master_port=3306,

->master_user='repl',

->master_password='123456',

->master_log_file='mysql-bin.000001',

->master_log_pos=107;

Query OK, 0 rows affected (0.19 sec)

这里的master_log_file和master_log_pos对应主数据库master中的值。

6、从库开启从数据库复制功能。

mysql>stop slave;  -------停止

mysql>start slave;  --------启动

Query OK, 0 rows affected (0.00 sec)

到这一步一本已经成功了；现在你可以通过查看slave的状态来看看是否成功；

7、查看slave状态
show slave status;
//Slave_IO_Running 与 Slave_SQL_Running 状态都要为Yes
show processlist;
//应该有两行state值为：
Has read all relay log; waiting for the slave I/O thread to update it
Waiting for master to send event

五、测试是否成功

1、主库：创建表、添加数据

mysql> create table share(year int);  -----------新建表名share   字段名 year

Query OK, 0 rows affected (0.16 sec)  

mysql> insert into sharevalues(2016);----------- 在表share中插入数据 2016

Query OK, 1 row affected (0.13 sec)

2、从库：查看从库表、数据

mysql> show tables; -----查看表
+----------------+
| Tables_in_test |
+----------------+
| share|
+----------------+
2 rows in set (0.00 sec)

mysql> select * fromshare;------查看数据
+------+
| year|
+------+
| 2016 |
+------+
1 row in set (0.00 sec)

同样的道理可以布置第二个、第三个......等多个从数据库；

六、错误现象及问题分析

主数据库----master 

从数据库-----slave

问题汇总：

进入slave服务器，运行：
mysql> show slave status;
        .......
            Relay_Log_File: localhost-relay-bin.000535
            Relay_Log_Pos: 21795072
    Relay_Master_Log_File: localhost-bin.000094
Slave_IO_Running: Yes
Slave_SQL_Running: No
          Replicate_Do_DB:
      Replicate_Ignore_DB:
      ......
解决办法一、
Slave_SQL_Running: No
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后，事务回滚造成的.

一般是事务回滚造成的：
解决办法：
mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;
解决办法二、
首先停掉Slave服务： stop slave
到主服务器上查看主机状态：
记录File和Position对应的值
进入master
mysql> show master status;
+----------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| localhost-bin.000094 | 33622483 |              |                  |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
然后到slave服务器上执行手动同步：

mysql> change master to
> master_host='master_ip',
> master_user='user',
> master_password='pwd',
> master_port=3306,
> master_log_file=localhost-bin.000094',
> master_log_pos=33622483 ;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
........
          Master_Log_File: localhost-bin.000094
      Read_Master_Log_Pos: 33768775
            Relay_Log_File: localhost-relay-bin.000537
            Relay_Log_Pos: 1094034
    Relay_Master_Log_File: localhost-bin.000094
          Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
手动同步需要停止master的写操作！
查看mysql主从配置的状态及修正 slave不启动问题
1、查看master的状态
show master status;  //Position不应该为0
show processlist; 
//state状态应该为Has sent all binlog to slave; waiting for binlog to be updated
2、查看slave状态
show slave status;
//Slave_IO_Running 与 Slave_SQL_Running 状态都要为Yes
show processlist;
//应该有两行state值为：
Has read all relay log; waiting for the slave I/O thread to update it
Waiting for master to send event

3、错误日志
MySQL安装目录 /usr/local/mysql
MySQL日志目录 /usr/local/mysql/data/  形如，Hostname.err

4、Change master to
如果从库的Slave未启动，Slave_IO_Running为NO。
可能是主库是的master的信息有变化，
查看主库show master status;
记录下File,Position字段，假设为‘mysql-bin.000004’,98;
在从库执行：

mysql>stop slave;
mysql>change master to master_log_file='mysql-bin.000004',master_log_pos=98;
mysql>start slave;

5、SET global sql_slave_skip_counter=n;
如果从库的slave_sql_running为NO。
Err文件中记录：
Slave:Error "Duplicate entry '1' for key 1" on query.....
可能是master未向slave同步成功，但slave中已经有了记录。造成的冲突可以在从库上执行
set global sql_slave_skip_counter=n;
跳过几步。再restart slave就可以了。

6、同步错误处理
发现mysql slave服务器经常因为一些特殊字符或者符号产生的更新语句报错，整个同步也会因此而卡在那，最初的办法只是手动去出错的机器执行下面三条SQL语句，跳过错误即可。

mysql>slave stop;
mysql>set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql>slave start;

PS：本人多次遇到从数据库的同步进程自动停掉的问题，有时简单通过slave stop,slave start即可解决。有时slave start启动后又会自动停掉，这时使用 change master重设主数据库信息的方式解决了问题。

说明：
Slave_IO_Running：连接到主库，并读取主库的日志到本地，生成本地日志文件
Slave_SQL_Running:读取本地日志文件，并执行日志里的SQL命令。