高性能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，根据自己情况设置，主从不重复即可 |
# vim /etc/my.cnf [mysqld] log-bin=mysql-bin #开启二进制日志 server-id=3 #服务器唯一ID，根据自己情况设置，主从不重复即可 |
3、重启两台服务器的MySQL：
# /etc/init.d/mysql restart |
# /usr/local/mysql/bin/mysql -uroot -p mysql> grant replication slave on *.* to 'slave'@'%' identified by 'mysql'; # 一般不用root账号 |
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：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> |