MySQL主从完全配置

 高性能MySQL需要解决的主要有两个问题，即如何实现数据共享或同步数据，另一个是如何处理failover；数据共享一般的解决方案是通过SAN来实现，而数据同步可以通过rsync软件或DRBD技术来实现；failover的意思是当服务器死机或出现错误时可以自动切换到其它备用的服务器，不影响服务器上业务系统的运行。

    主从复制是MySQL自身提供的一种高可用解决方案，数据同步方法采用的是MySQL replication技术。MySQL replication就是一个日志的复制过程，在复制过程中一个服务器充当住服务器，而一个或多个其它服务器充当从服务器，简单说就是从服务器到主服务器拉去二进制日志文件，然后再将日志文件解析成相应的SQL在从服务器重新执行一遍主服务器的操作，通过这种方式保证数据的一致性。

    MySQL replication技术仅仅提供了日志的同步执行功能，而从服务器只能提供读操作，并且当主服务器故障时，必须通过手动来处理failover，通常的做法是将一台从服务器改为主服务器。

操作步骤：

1、修改主服务器master：

# vim /etc/my.cnf

[mysqld]

log-bin=mysql-bin      #开启二进制日志

server-id=2      #服务器唯一ID，根据自己情况设置，主从不重复即可

2、修改从服务器slave：

# vim /etc/my.cnf

[mysqld]

log-bin=mysql-bin      #开启二进制日志

server-id=3          #服务器唯一ID，根据自己情况设置，主从不重复即可

3、重启两台服务器的MySQL：

# /etc/init.d/mysql restart

4、在主服务器上建立账户并授权slave：

# /usr/local/mysql/bin/mysql -uroot -p

mysql> grant replication slave on *.* to 'slave'@'%' identified by 'mysql';   # 一般不用root账号

查看MySQL上用户及各自权限：

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

mysql> show grants for 'cactiuser'@'%'; 

5、登陆主服务器的MySQL，查询master的状态：

mysql> FLUSH TABLES WITH READ LOCK; #如果主服务器中有数据则先对主服务器加锁

# mysqldump -uroot -p mobileadmin > mobileadmin.sql;    #导出数据库

mysql> show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      120 |              |                  |                   |

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

1 row in set (0.00 sec)

注意：执行完后不要对主服务器做任何操作，防止主服务器状态发生变化。

6、配置从服务器slave：

mysql > source /home/zhangxiang/mobileadmin.sql;          #导入数据库

# mysql -uroot -p mobileadmin < mobileadmin.sql;          #导入数据库

mysql > change master to

-> master_host='192.168.147.130',

-> master_user='slave',

-> master_password='mysql',

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

-> master_log_pos=120;

mysql > 

mysql > start slave;

7、检查从服务器复制功能状态：

mysql > show slave status\G

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

              Slave_IO_State: Waiting for master to send event

              Master_Host: 192.168.147.130  #主服务器地址

              Master_User: slave   #授权帐户名，尽量避免使用root

              Master_Port: 3306    #数据库端口，部分版本没有此行

              Connect_Retry: 60

              Master_Log_File: mysql-bin.0000041

              Read_Master_Log_Pos: 120    //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos

              Relay_Log_File: ddte-relay-bin.000001

              Relay_Log_Pos: 4

              Relay_Master_Log_File: mysql-bin.000001

              Slave_IO_Running: Yes    //此状态必须YES

              Slave_SQL_Running: Yes     //此状态必须YES

                 ........................

注意：Slave_IO及Slave_SQL进程必须正常运行，否则都是错误状态。

停止slave：stop slave;

重置slave：reset slave;

8、主从服务器测试：

主服务器MySQL建一个数据库，并在这个库中建表插入一条数据：

  mysql> create database hi_db;

  Query OK, 1 row affected (0.00 sec)

  mysql>  create table hi_tb(id int(3),name char(10));

  Query OK, 0 rows affected (0.00 sec)

  mysql> insert into hi_tb values(001,'bobu');

  Query OK, 1 row affected (0.00 sec)

  mysql> show databases;

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

   | Database           |

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

   | information_schema |

   | hi_db                |

   | mysql                |

   | test                 |

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

   4 rows in set (0.00 sec)

从服务器MySQL查询：

   mysql> show databases;

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

   | Database               |

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

   | information_schema |

   | hi_db                 |       #注意这，大家看到了吧

   | mysql                 |

   | test          |

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

   4 rows in set (0.00 sec)

   mysql> use hi_db

   Database changed

   mysql> select * from hi_tb;           #查看主服务器上新增的具体数据

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

   | id   | name |

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

   |    1 | bobu |

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

   1 row in set (0.00 sec)

mysql> UNLOCK TABLES; #对主服务器释放锁

主从配置到此结束，因为公司需要有了如下配置过程

9、windows server从配置(因为公司需要，本人测试过该方法有效；保证主从版本一致)

为了方便，安装的时候选择完全安装吧(个人水平牛X的自己定义喽)

MySQL安装完后不会自动配置环境变量，所以在命令行下mysql命令不能正常使用。

MySQL的配置文件：

编辑安装目录下的my-default.ini文件(如我的路径就是：C:\Program Files\MySQL\MySQL Server 5.6\my-default.ini)

修改my-default.ini文件名称为my.ini

[mysqld]

log-bin=mysql-bin

server-id=4

启动MySQL服务：

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqld.exe --console  #启动win版的MySQL

2016-03-30 16:57:34 0 [Warning] TIMESTAMP with implicit DEFAULT value is depreca

ted. Please use --explicit_defaults_for_timestamp server option (see documentati

on for more details).

2016-03-30 16:57:34 0 [Note] mysqld.exe (mysqld 5.6.28-log) starting as process

3916 ...

2016-03-30 16:57:34 3916 [Note] Plugin 'FEDERATED' is disabled.

2016-03-30 16:57:34 3916 [Note] InnoDB: Using atomics to ref count buffer pool p

ages

2016-03-30 16:57:34 3916 [Note] InnoDB: The InnoDB memory heap is disabled

2016-03-30 16:57:34 3916 [Note] InnoDB: Mutexes and rw_locks use Windows interlo

cked functions

2016-03-30 16:57:34 3916 [Note] InnoDB: Memory barrier is not used

2016-03-30 16:57:34 3916 [Note] InnoDB: Compressed tables use zlib 1.2.3

2016-03-30 16:57:34 3916 [Note] InnoDB: Not using CPU crc32 instructions

2016-03-30 16:57:34 3916 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2016-03-30 16:57:34 3916 [Note] InnoDB: Completed initialization of buffer pool

2016-03-30 16:57:34 3916 [Note] InnoDB: Highest supported file format is Barracu

da.

2016-03-30 16:57:34 3916 [Note] InnoDB: 128 rollback segment(s) are active.

2016-03-30 16:57:34 3916 [Note] InnoDB: Waiting for purge to start

2016-03-30 16:57:34 3916 [Note] InnoDB: 5.6.28 started; log sequence number 1625

987

2016-03-30 16:57:35 3916 [Note] Server hostname (bind-address): '*'; port: 3306

2016-03-30 16:57:46 3916 [Note] IPv6 is not available.

2016-03-30 16:57:46 3916 [Note]   - '0.0.0.0' resolves to '0.0.0.0';

2016-03-30 16:57:46 3916 [Note] Server socket created on IP: '0.0.0.0'.

2016-03-30 16:57:46 3916 [Note] Event Scheduler: Loaded 0 events

2016-03-30 16:57:46 3916 [Note] mysqld.exe: ready for connections.

Version: '5.6.28-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

2016-03-30 17:00:50 3916 [Warning] Neither --relay-log nor --relay-log-index wer

e used; so replication may break when this MySQL server acts as a slave and has

his hostname changed!! Please use '--relay-log=admin-b2618d0d7-relay-bin' to avo

id this problem.

2016-03-30 17:00:50 3916 [Note] 'CHANGE MASTER TO executed'. Previous state mast

er_host='', master_port= 3306, master_log_file='', master_log_pos= 4, master_bin

d=''. New state master_host='192.168.147.130', master_port= 3306, master_log_fil

e='mysql-bin.000001', master_log_pos= 560, master_bind=''.

2016-03-30 17:01:43 3916 [Warning] Storing MySQL user name or password informati

on 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.

2016-03-30 17:01:43 3916 [Warning] Slave SQL: If a crash happens this configurat

ion does not guarantee that the relay log info will be consistent, Error_code: 0

2016-03-30 17:01:43 3916 [Note] Slave SQL thread initialized, starting replicati

on in log 'mysql-bin.000001' at position 560, relay log '.\admin-b2618d0d7-relay

-bin.000001' position: 4

2016-03-30 17:01:43 3916 [Note] Slave I/O thread: connected to master 'slave@192

.168.147.130:3306',replication started in log 'mysql-bin.000001' at position 560

启动MySQL客户端：

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysql -uroot #安装完MySQL默认密码为空

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. A

Oracle is a registered trademark of Oracle Corporation an

affiliates. Other names may be trademarks of their respec

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the cur

mysql> change master to

    -> master_host='192.168.147.130',

    -> master_user='slave',

    -> master_password='mysql',

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

    -> master_log_pos=560;

Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.147.130

                  Master_User: slave

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 560

               Relay_Log_File: admin-b2618d0d7-relay-bin.000002

                Relay_Log_Pos: 283

        Relay_Master_Log_File: mysql-bin.000001

             Slave_IO_Running: Yes

            Slave_SQL_Running: 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: 560

              Relay_Log_Space: 466

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

                  Master_UUID: bea0ebe3-f688-11e5-9522-000c2920c709

             Master_Info_File: C:\Program Files\MySQL\MySQL Server 5.6\data\mast

er.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the sla

ve I/O thread to update it

           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

1 row in set (0.00 sec)

mysql>

mysql> show databases;

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

| Database           |

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

| information_schema |

| hi_db             |

| mysql              |

| performance_schema |

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

4 rows in set (0.03 sec)

mysql>