Mysql主从数据库配置

1.主库里配置如下：

[mysql]
socket=/Disk2_sdb/mysql/mysql.sock     //socket要改
[mysqldump]
socket=/Disk2_sdb/mysql/mysql.sock      //socket要改
[mysqld]
server-id=55
log-bin=/Disk2_sdb/mysql/mysql-bin.log
binlog-do-db=redwine
binlog-ignore-db=mysql
replicate-do-db=redwine      //这个参数只有当slave的时候有用
replicate-ignore-db=mysql   //这个参数只有当slave的时候有用
binlog_cache_size=1M
expire_logs_days=7
slave_skip_errors=1032,1062,126,1114,1146,1048,1396

binlog_format=MIXED  #设置 binlog 的格式为 MIXED    //次格式binlog减少网络开销

datadir=/Disk2_sdb/mysql    //从新定义数据库的位置的时候，要改新位置的目录权限为mysql
socket=/Disk2_sdb/mysql/mysql.sock   //socket要改
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

2.从库的配置

 [mysql]
socket=/Disk2_sdb/mysql/mysql.sock
[mysqldump]
socket=/Disk2_sdb/mysql/mysql.sock
[mysqld]
server-id=56
replicate-do-db=redwine
replicate-ignore-db=mysql
datadir=/Disk2_sdb/mysql
socket=/Disk2_sdb/mysql/mysql.sock
symbolic-links=0
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 3.在主库创建一个同步的的用户

Mysql> grant replication slave on *.* to 'python'@'192.168.1.%' identified by '123456';

4.在从库上开启slave

 mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.110', MASTER_PORT=3306, MASTER_USER='python', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=106;

mysql> start slave; Query OK, 0 rows affected (0.00 sec)

==========================================================================

常见问题：

一。配置不同步的库和表是在slave端配置。

二。正常显示

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: x.x.x.x
                  Master_User: python
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 106
               Relay_Log_File: mysqld-relay-bin.000004
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: redwine
          Replicate_Ignore_DB: mysql
 

 三。重启主库，后要手动重启从库的，stop slave;start slave，会自己同步连接主库！

===============================================================================

常见问题：

一。动态同步数据：（主从在使用）锁定主表不让写入

mysql>flush tables with read lock;  #数据库锁表，不让写数据

mysql>show master status; #查看MASTER状态（这两个值File和Position）

mysql>unlock tables;     #从服务器启动好后，解除锁定

二。[Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.（在调整slave_net_timeout=10之后，会出现上面的警告）

在从服务器执行下面的语句，先停止slave，然后执行下面的语句启动slave。

START SLAVE USER='userName' PASSWORD='password'  (重启mysql失效，必须重启后，再执行)

三。[Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0

在从服务器上，增加下面两个配置：

master_info_repository=table
relay_log_info_repository=table