about 7 results (0.02 seconds)

MySQL按拼音首字母排序的SQL语句

by LauCyun Jul 12,2017 12:25:16 20,545 views

在处理使用MySQL时,数据表采用utf8字符集,使用中发现中文不能直接按照拼音排序。

 

如果数据表table1的某字段name的字符编码是latin1_swedish_ci

select * from `table1` order by birary(name) asc;

如果数据表table1的某字段name的字符编码是utf8_general_ci

SELECT name FROM `table1` WHERE 1 ORDER BY CONVERT(name USING gbk) COLLATE gbk_chinese_ci ASC;

 

...

Tags Read More..


MySQL如何跨机器迁移数据?

by LauCyun Dec 12,2016 10:07:01 8,462 views

经常会遇到如此需求,需把A主机上的MySQL数据库所有迁移到B主机上,或者部分数据库,所以接下来将介绍迁移所有数据库和迁移单个数据库时的数据迁移步骤。

1 实验环境

A主机(源主机):

  • IP地址:192.168.0.191
  • 系统版本:CentOS 6.8
  • MySQL版本:5.5
  • 源主机有以下数据库
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | laucyun            |
    | mysql              |
    | performance_schema |
    +--------------------+
    4 rows in set (0.00 sec)
    查看数据库laucyun内容:
    mysql> show tables;
    +-------------------+
    | Tables_in_laucyun |
    +-------------------+
    | users             |
    +-------------------+
    1 row in set (0.00 sec)
    
    mysql> select * from users;
    +----+----------+----------+----------------+
    | id | username | password | email          |
    +----+----------+----------+----------------+
    |  1 | laucyun  | 123456   | liu@liuker.xyz |
    +----+----------+----------+----------------+
    1 row in set (0.00 sec)
    
    其中,数据库laucyun中有数据库表usersusers表中有一条记录。

B主机(目标主机):

  • IP地址:192.168.0.192
  • 系统版本:CentOS 6.8
  • MySQL版本:5.5
  • 目标主机有以下数据库:
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    +--------------------+
    3 rows in set (0.00 sec)

源主机比目标主机多一个名为laucyun的数据库。

OK,开始环境介绍完后,先介绍迁移所有数据。

2 迁移所有数据库

目前实验环境中,源主机比目标主机多一个名为laucyun的数据库。

在源主机备份所有数据库:

$ mysqldump -uroot -p --all-databases > /home/all_databases.bak

提示输入root用户的密码,备份文件信息如下:

$ ll
total 552
-rw-r--r-- 1 root root 561449 Dec 31 13:02 all_databases.bak

拷贝备份文件all_databases.bak到目标主机:

$ scp all_databases.bak root@192.168.0.192:/home/

在目标主机还原所有数据库:

$ mysql -uroot -p < /home/all_databases.bak

提示输入root用户的密码。

验证是否成功:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| laucyun            |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> use laucyun;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_laucyun |
+-------------------+
| users             |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from users;
+----+----------+----------+----------------+
| id | username | password | email          |
+----+----------+----------+----------------+
|  1 | laucyun  | 123456   | liu@liuker.xyz |
+----+----------+----------+----------------+
1 row in set (0.00 sec)

由此可见,目标主机上已经备份成功了源主机中所有数据库。

注意:当迁移所有数据库时,不用提前在目标主机创建好所有数据库。

3 迁移某个数据库

目前实验环境中,源主机比目标主机多一个名为laucyun的数据库。

在源主机备份数据库laucyun

$ mysqldump -uroot -p laucyun > /home/laucyun.bak

提示输入root用户的密码,备份文件信息如下:

$ ll
total 4
-rw-r--r-- 1 root root 1982 Dec 31 13:52 laucyun.bak

拷贝备份文件laucyun.bak到目标主机:

$ scp laucyun.bak root@192.168.0.192:/home/

在目标主机中创建好数据库laucyun

mysql> CREATE DATABASE `laucyun` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

在目标主机还原数据库laucyun

$ mysql -uroot -p laucyun < /home/laucyun.bak

提示输入root用户的密码。

验证是否成功:

mysql> show tables;
+-------------------+
| Tables_in_laucyun |
+-------------------+
| users             |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from users;
+----+----------+----------+----------------+
| id | username | password | email          |
+----+----------+----------+----------------+
|  1 | laucyun  | 123456   | liu@liuker.xyz |
+----+----------+----------+----------------+
1 row in set (0.00 sec)

由此可见,目标主机上已经备份成功了源主机中数据库laucyun

注意:当迁移某个数据库时,需要提前在目标主机中创建好该数据库。

...

Tags Read More..


MySQL主主同步复制

by LauCyun Jun 29,2016 13:50:04 13,254 views

MySQL主主同步跟MySQL主从同步类似,只是再另一台上反向再做一次主从同步。只不过MySQL的配置文件有些地方需要注意一下。

实验环境:

  • 系统:CentOS 6.5
  • MySQL版本:MySQL 5.5.48
  • master 1:192.168.0.211
  • master 2:192.168.0.212

注意下面几点:

  • 要保证同步服务期间之间的网络联通。即能相互ping通,能使用对方授权信息连接到对方数据库(防火墙开放3306端口)。
  • 关闭selinux。
  • 同步前,双方数据库中需要同步的数据要保持一致。这样,同步环境实现后,再次更新的数据就会如期同步了。

1 配置master1

1.1 修改master1数据库的my.cnf文件

[root@master1 ~]# vim /etc/my.cnf

[mysqld]配置区域添加下面内容:

#
# master 1 (192.168.0.211)
#
server-id = 1
log_bin = mysql-bin
binlog-do-db = test
binlog-ignore-db = mysql,information_schema
replicate-do-db = test
replicate-ignore-db = mysql,information_schema
log-slave-updates = ON
expire_logs_days = 365
sync_binlog = 1
auto-increment-increment = 2
auto-increment-offset = 1

参数说明:

  • server-id:数据库唯一标识,主从的标识号绝对不能重复。
  • log_bin:开启bin-log,并指定文件目录和文件名前缀。
  • binlog-do-db:只将对应的数据库变动写入二进制文件。如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
  • binlog-ignore-db:不同步的数据库。如果有多个不同步库,就以此格式另写几行;也可以在一行,中间逗号隔开。
  • replicate-do-db:需要同步的数据库。如果不指明同步那些库,就去掉这行,表示所有库的同步(除了ignore忽略的库)。
  • replicate-ignore-db:不需要同步的数据库。如果有多个数据库可用逗号分隔,或者使用多个replicate-ignore-db选项。一般为了保证主主同步不冲突。
  • log-slave-updates:中继日志执行之后将变化写入自己的二进制文件。
  • expire_logs_days:日志文件过期天数,默认是 0,表示不过期。
  • sync_binlog:确保binlog日志写入后与硬盘同步。
    • 注意:

      在主服务器上最重要的二进制日志设置是sync_binlog,这使得mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上,即使服务器崩溃也会把事件写入日志中。

      sync_binlog这个参数是对于MySQL系统来说是至关重要的,他不仅影响到Binlog对MySQL所带来的性能损耗,而且还影响到MySQL中数据的完整性。对于sync_binlog参数的各种设置的说明如下:

      • sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。
      • sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
    • 在MySQL中系统默认的设置是sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。而当设置为sync_binlog=1的时候,是最安全但是性能损耗最大的设置。因为当设置为sync_binlog=1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据没有任何实质性影响。

      从以往经验和相关测试来看,对于高并发事务的系统来说,sync_binlog设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

  • auto-increment-increment:设定为主服务器的数量,防止auto_increment字段重复。
  • auto-increment-offset:自增长字段的初始值,在多台master环境下,不会出现自增长ID重复。

1.2 设置数据同步权限

创建一个同步用的用户:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'sync'@'192.168.0.212' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

检查是否创建成功,如果成功,则如下;反之:

mysql> show grants for 'sync'@'192.168.0.212';
+-----------------------------------------------------------------------------------------------------------------------------+
| Grants for sync@192.168.0.212                                                                                               |
+-----------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'sync'@'192.168.0.212' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

1.3 重启mysql

[root@master1 ~]# service mysqld restart

1.4 查看master1状态

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000001 |      107 |              | mysql,information_schema,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)

注意FilePosition项,master2需要这两项参数。

到此为止,master1配置完成,接下来就是配置master2。

2 配置master2

2.1 修改master2数据库的my.cnf文件

[root@master1 ~]# vim /etc/my.cnf

[mysqld]配置区域添加下面内容:

#
# master 2 (192.168.0.212)
#
server-id = 2
log_bin = mysql-bin
binlog-do-db = test
binlog-ignore-db = mysql,information_schema
replicate-do-db = test
replicate-ignore-db = mysql,information_schema
log-slave-updates = ON
expire_logs_days = 365
sync_binlog = 1
auto-increment-increment = 2
auto-increment-offset = 2

2.2 设置数据同步权限

创建一个同步用的用户:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'sync'@'192.168.0.211' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

检查是否创建成功,如果成功,则如下;反之:

mysql> show grants for 'sync'@'192.168.0.211';
+-----------------------------------------------------------------------------------------------------------------------------+
| Grants for sync@192.168.0.211                                                                                               |
+-----------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'sync'@'192.168.0.211' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2.3 重启mysql

[root@master2 ~]# service mysqld restart

2.4 查看master2状态

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000001 |      107 |              | mysql,information_schema,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)

到此为止,master2配置完成。

3 配置双主同步

设置master1从master2同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.212',MASTER_PORT=3306,MASTER_USER='sync',MASTER_PASSWORD='123456';
Query OK, 0 rows affected (0.86 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.212
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 539
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 685
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: mysql,information_schema
           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: 539
              Relay_Log_Space: 1227
              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
1 row in set (0.00 sec)

如出现以下两项,则说明配置成功!

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
......
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

设置master2从master1同步

mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.211',MASTER_PORT=3306,MASTER_USER='sync',MASTER_PASSWORD='123456';
Query OK, 0 rows affected (0.40 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.211
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 187
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 333
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test
          Replicate_Ignore_DB: mysql,information_schema
           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: 187
              Relay_Log_Space: 636
              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: 1
1 row in set (0.00 sec)

如出现以下两项,则说明配置成功!

​mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
......
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

4 双主同步复制测试

4.1 新建数据库表测试

在maste1新建数据库表users

mysql> create table users(
    -> id int primary key auto_increment,
    -> name varchar(50) not null,
    -> email varchar(50)
    -> );
Query OK, 0 rows affected (0.25 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)

在msater2上验证:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)

说明同步成功!

4.2 在master1插入数据测试

在master1分别插入2条数据:

mysql> INSERT INTO users(name,email) VALUES('laucyun','liu@liuker.xyz');
Query OK, 1 row affected (0.26 sec)

mysql> SELECT * FROM users;
+----+---------+----------------+
| id | name    | email          |
+----+---------+----------------+
|  1 | laucyun | liu@liuker.xyz |
+----+---------+----------------+
1 row in set (0.00 sec)

mysql> INSERT INTO users(name,email) VALUES('test1','test1@liuker.xyz');
Query OK, 1 row affected (0.28 sec)

mysql> SELECT * FROM users;
+----+---------+------------------+
| id | name    | email            |
+----+---------+------------------+
|  1 | laucyun | liu@liuker.xyz   |
|  3 | test1   | test1@liuker.xyz |
+----+---------+------------------+
2 rows in set (0.00 sec)

在master2上验证:

mysql> SELECT * FROM users;
+----+---------+----------------+
| id | name    | email          |
+----+---------+----------------+
|  1 | laucyun | liu@liuker.xyz |
+----+---------+----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM users;
+----+---------+------------------+
| id | name    | email            |
+----+---------+------------------+
|  1 | laucyun | liu@liuker.xyz   |
|  3 | test1   | test1@liuker.xyz |
+----+---------+------------------+
2 rows in set (0.00 sec)

说明同步成功!

4.3 在master2插入数据测试

在master2分别插入2条数据:

mysql> INSERT INTO users(name,email) VALUES('test2','test2@liuker.xyz');
Query OK, 1 row affected (0.18 sec)

mysql> INSERT INTO users(name,email) VALUES('test3','test3@liuker.xyz');
Query OK, 1 row affected (0.27 sec)

mysql> SELECT * FROM users;
+----+---------+------------------+
| id | name    | email            |
+----+---------+------------------+
|  1 | laucyun | liu@liuker.xyz   |
|  3 | test1   | test1@liuker.xyz |
|  4 | test2   | test2@liuker.xyz |
|  6 | test3   | test3@liuker.xyz |
+----+---------+------------------+
4 rows in set (0.00 sec)

在master1上验证:

mysql> SELECT * FROM users;
+----+---------+------------------+
| id | name    | email            |
+----+---------+------------------+
|  1 | laucyun | liu@liuker.xyz   |
|  3 | test1   | test1@liuker.xyz |
|  4 | test2   | test2@liuker.xyz |
|  6 | test3   | test3@liuker.xyz |
+----+---------+------------------+
4 rows in set (0.00 sec)

说明同步成功!

现在,主主同步环境已经实现了!

...

Tags Read More..


MySQL主从同步复制

by LauCyun Jun 26,2016 13:30:13 13,671 views

实验环境:

  • 系统:CentOS 6.5
  • MySQL版本:MySQL 5.5.48
  • master:172.17.0.2
  • slave:172.17.0.3

注意下面几点:

  • 要保证同步服务期间之间的网络联通。即能相互ping通,能使用对方授权信息连接到对方数据库(防火墙开放3306端口)。
  • 关闭selinux。
  • 同步前,双方数据库中需要同步的数据要保持一致。这样,同步环境实现后,再次更新的数据就会如期同步了。

1 配置master

1.1 修改master数据库的my.cnf文件

[root@master ~]# vim /etc/my.cnf

[mysqld]配置区域添加下面内容:

#
# master
#
server-id = 1
log_bin = mysql-bin
binlog-do-db = demo
binlog-ignore-db = mysql,information_schema
replicate-do-db = demo
replicate-ignore-db = mysql,information_schema
expire_logs_days = 365
sync_binlog = 1
binlog_format = mixed

参数说明:

  • server-id:数据库唯一标识,主从的标识号绝对不能重复。
  • log_bin:开启bin-log,并指定文件目录和文件名前缀。
  • binlog-do-db:只将对应的数据库变动写入二进制文件。如果有多个数据库可用逗号分隔,或者使用多个binlog-do-db选项
  • binlog-ignore-db:不同步的数据库。如果有多个不同步库,就以此格式另写几行;也可以在一行,中间逗号隔开。
  • replicate-do-db:需要同步的数据库。如果不指明同步那些库,就去掉这行,表示所有库的同步(除了ignore忽略的库)。
  • replicate-ignore-db:不需要同步的数据库。如果有多个数据库可用逗号分隔,或者使用多个replicate-ignore-db选项。一般为了保证主主同步不冲突。
  • expire_logs_days:日志文件过期天数,默认是 0,表示不过期。
  • sync_binlog:确保binlog日志写入后与硬盘同步。
    • 注意:

      在主服务器上最重要的二进制日志设置是sync_binlog,这使得mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上,即使服务器崩溃也会把事件写入日志中。

      sync_binlog这个参数是对于MySQL系统来说是至关重要的,他不仅影响到Binlog对MySQL所带来的性能损耗,而且还影响到MySQL中数据的完整性。对于sync_binlog参数的各种设置的说明如下:

      • sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。
      • sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
    • 在MySQL中系统默认的设置是sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。而当设置为sync_binlog=1的时候,是最安全但是性能损耗最大的设置。因为当设置为sync_binlog=1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据没有任何实质性影响。

      从以往经验和相关测试来看,对于高并发事务的系统来说,sync_binlog设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

  • binlog_formatbin-log日志文件格式,设置为MIXED可以防止主键重复。

1.2 设置数据同步权限

创建一个复制用的用户:

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT,REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

检查是否创建成功,如果成功,则如下;反之:

mysql> show grants for repl@'%';
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for repl@%                                                                                                       |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

1.3 重启mysql

[root@master ~]# service mysqld restart

1.4 查看master状态

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+---------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000001 |      107 |              | mysql,information_schema,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)

注意FilePosition项,从服务器需要这两项参数。

到此为止,master配置完成,接下来就是配置slave。

2 配置slave

2.1 修改slave数据库的my.cnf文件

[root@slave ~]# vim /etc/my.cnf

[mysqld]配置区域添加下面内容:

#
# slave
#
server-id = 2
log_bin = mysql-bin
binlog-do-db = demo
binlog-ignore-db = mysql,information_schema
replicate-do-db = demo
replicate-ignore-db = mysql,information_schema
slave-skip-errors = all

只针对某些库的某张表进行同步时。如下,只同步demo库的users表:

replicate-do-db = demo
replicate-wild-do-table = demo.users  # 当只同步几个或少数表时,可以这样设置;如果同步的库的表比较多时,就不能这样。

2.2 配置主从同步

先在slave用master授权用户连接master:

mysql> CHANGE MASTER TO MASTER_HOST='172.17.0.2', MASTER_USER='repl', MASTER_PASSWORD='123456';
Query OK, 0 rows affected (0.04 sec)

启动slave同步:

mysql> START SLAVE;
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看slave的状态:

mysql> SHOW SLAVE STATUS \G;   -- \G用来代替";",能把查询结果按键值的方式显示
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.17.0.2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 253
        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: 107
              Relay_Log_Space: 410
              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: 1
1 row in set (0.00 sec)

看到以下内容表示配置成功:

...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

到此为止,slave配置完成。

3 主从同步复制测试

3.1 新建数据库

在master新建数据库demo:

mysql> create database demo;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

在slave上验证:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| demo               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

说明同步成功!

3.2 新建数据库表和插入数据

在master的数据库demo中新建表users,并插入一条数据:

mysql> create table users(
    -> id int primary key auto_increment,
    -> name varchar(50) not null,
    -> email varchar(50)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)

mysql> insert into users(name,email) values('laucyun','liu@liuker.xyz');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+----+---------+----------------+
| id | name    | email          |
+----+---------+----------------+
|  1 | laucyun | liu@liuker.xyz |
+----+---------+----------------+
1 row in set (0.00 sec)

在slave上验证:

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| users          |
+----------------+
1 row in set (0.00 sec)

mysql> select * from users;
+----+---------+----------------+
| id | name    | email          |
+----+---------+----------------+
|  1 | laucyun | liu@liuker.xyz |
+----+---------+----------------+
1 row in set (0.00 sec)

说明同步成功!

现在,主从同步环境已经实现了!

...

Tags Read More..


MySQL复制

by LauCyun Jun 25,2016 10:47:47 6,007 views

MySQL内建的复制功能是构建大型,高性能应用程序的基础。将MySQL的数据分布到多个系统上去,这种分布的机制,是通过将MySQL的某一台主机的数据复制到其它主机(slaves)上,并重新执行一遍来实现的。复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知新的更新。

注意:
在进行MySQL复制时,所有对复制中的表的更新必须在主服务器上进行。否则必须要小心,以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

1 MySQL支持那些复制

  • 基于语句的复制: 在主服务器上执行的SQL语句,在从服务器上执行同样的语句。MySQL默认采用基于语句的复制,效率比较高。一旦发现没法精确复制时,会自动选着基于行的复制。 
  • 基于行的复制:把改变的内容复制过去,而不是把命令在从服务器上执行一遍. 从MySQL5.0开始支持
  • 混合类型的复制: 默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。

2 MySQL复制能解决的问题

  • 数据分布 (Data distribution )
  • 负载平衡(load balancing)
  • 数据备份(Backups) ,保证数据安全
  • 高可用性和容错行(High availability and failover)
  • 实现读写分离,缓解数据库压力

3 MySQL主从复制原理

master服务器将数据的改变记录二进制binlog日志,当master上的数据发生改变时,则将其改变写入二进制日志中;salve服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变,如果发生改变,则开始一个I/OThread请求master二进制事件,同时主节点为每个I/O线程启动一个dump线程,用于向其发送二进制事件,并保存至从节点本地的中继日志中,从节点将启动SQL线程从中继日志中读取二进制日志,在本地重放,使得其数据和主节点的保持一致,最后I/OThread和SQLThread将进入睡眠状态,等待下一次被唤醒。

注意几点:

  • master将操作语句记录到binlog日志中,然后授予slave远程连接的权限(master一定要开启binlog二进制日志功能;通常为了数据安全考虑,slave也开启binlog功能)。
  • slave开启两个线程:IO线程和SQL线程。其中:IO线程负责读取master的binlog内容到中继日志relay log里;SQL线程负责从relay log日志里读出binlog内容,并更新到slave的数据库里,这样就能保证slave数据和master数据保持一致了。
  • MySQL复制至少需要两个MySQL的服务,当然MySQL服务可以分布在不同的服务器上,也可以在一台服务器上启动多个服务。
  • MySQL复制最好确保master和slave服务器上的MySQL版本相同(如果不能满足版本一致,那么要保证master主节点的版本低于slave从节点的版本)
  • master和slave两节点间时间需同步

MySQL复制的流程图如下:


(来源:互联网)

如上图所示,MySQL复制过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前,master在二日志记录这些改变。MySQL将事务串行的写入二进制日志,即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后,master通知存储引擎提交事务。

第二部分就是slave将master的binary log拷贝到它自己的中继日志。首先,slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接,然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件,如果已经跟上master,它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

SQL slave thread(SQL从线程)处理该过程的最后一步。SQL线程从中继日志读取事件,并重放其中的事件而更新slave的数据,使其与master中的数据一致。只要该线程与I/O线程保持一致,中继日志通常会位于OS的缓存中,所以中继日志的开销很小。
此外,在master中也有一个工作线程:和其它MySQL的连接一样,slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的,也就是说master上的并行更新操作不能在slave上并行操作。

4 MySQL复制的模式

  • 主从复制:主库授权从库远程连接,读取binlog日志并更新到本地数据库的过程;主库写数据后,从库会自动同步过来(从库跟着主库变)。具体参考:MySQL主从同步复制
  • 主主复制:主从相互授权连接,读取对方binlog日志并更新到本地数据库的过程;只要对方数据改变,自己就跟着改变。具体参考:MySQL主主同步复制

5 MySQL主从复制的优点

  • 在从服务器可以执行查询工作(即我们常说的读功能),降低主服务器压力;(主库写,从库读,降压)
  • 在从主服务器进行备份,避免备份期间影响主服务器服务;(确保数据安全)
  • 当主服务器出现问题时,可以切换到从服务器。(提升性能)

6 MySQL主从复制工作流程细节

MySQL支持单向、异步复制,复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。MySQL复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此,要进行复制,必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器上已经记录到其二进制日志的保存的更新。当一个从服务器连接主服务器时,它通知主服务器定位到从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,并在本机上执行相同的更新。然后封锁并等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器,在备份过程中主服务器可以继续处理更新。

MySQL使用3个线程来执行复制功能,其中两个线程(Sql线程和IO线程)在从服务器,另外一个线程(IO线程)在主服务器。
当发出START SLAVE时,从服务器创建一个I/O线程,以连接主服务器并让它发送记录在其二进制日志中的语句。主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以即为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中,即中继日志。第3个线程是SQL线程,由从服务器创建,用于读取中继日志并执行日志中包含的更新。在从服务器上,读取和执行更新语句被分成两个独立的任务。当从服务器启动时,其I/O线程可以很快地从主服务器索取所有二进制日志内容,即使SQL线程执行更新的远远滞后。

...

Tags Read More..