mysql之percona5.6.10masterslave…

本文以在最新的percona5.6.10版本在虚拟机上进行安装配置，给第一次接触percona一个手把手教的过程。以下内容，全部来自实际测试环境。

环境：

    系统：vm redhat5.4

    数据库：percona5.6.10

两个ip：

    master:192.168.66.111

    slave: 192.168.66.112

安装步骤和思路：

 

1.主从库安装及配置文件配置

  master库

  vi /etc/my.cnf

  log_bin       = mysql-bin

  server_id     = 111

  slave库

  vi /etc/my.cnf

  log_bin           = mysql-bin

  server_id         = 112

  relay_log         = /var/lib/mysql/mysql-relay-bin

  log_slave_updates = 1

  read_only         =1

2.主库和备库都需要建立账号：

  grant replication slave,replication client on *.* to repl@'192.168.66.%' identified by   'password';

3.slave启动复制：

  a.

    change master to master_host='192.168.66.111',master_user='repl',master_password='password',master_log_file='mysql-bin.000003',master_log_pos=0;

    其中日志文件需要根据master库文件编号来配置。

    b. 检查状态

    show slave status\G

    c. 开始

    start slave;

4. 检查状态

    show slave status\G

    show master status\G

    主从查看进程

    show processlist\G

5.数据库测试

  主库创建表且插入数据，从库查看

疑问：主库重启后两次查看的文件不一致

以下是具体过程：

步骤一：

主、从数据库安装（注意安装文件的先后顺序shared-client-server,不能颠倒）

[root@localhost local]# pwd

/usr/local

[root@localhost local]# ls -altr

total 64312

drwxr-xr-x   2 root root     4096 Aug   8   2008 src

drwxr-xr-x   2 root root     4096 Aug   8   2008 sbin

drwxr-xr-x   2 root root     4096 Aug   8   2008 libexec

drwxr-xr-x   2 root root     4096 Aug   8   2008 lib

drwxr-xr-x   2 root root     4096 Aug   8   2008 include

drwxr-xr-x   2 root root     4096 Aug   8   2008 games

drwxr-xr-x   2 root root     4096 Aug   8   2008 etc

drwxr-xr-x   2 root root     4096 Aug   8   2008 bin

drwxr-xr-x   4 root root     4096 May 22 00:23 share

-rw-r--r--   1 root root   982230 May 29 14:47 Percona-Server-shared-55-5.5.20-rel24.1.217.rhel5.i686.rpm

-rw-r--r--   1 root root   8597327 May 29 14:56 Percona-Server-client-55-5.5.20-rel24.1.217.rhel5.i686.rpm

-rw-r--r--   1 root root 21906622 May 29 15:05 Percona-Server-server-55-5.5.20-rel24.1.217.rhel5.i686.rpm

-rw-r--r--   1 root root   1077782 May 29 17:57 Percona-Server-shared-56-5.6.10-alpha60.2.324.rhel5.i686.rpm

-rw-r--r--   1 root root 10247953 May 29 18:04 Percona-Server-client-56-5.6.10-alpha60.2.324.rhel5.i686.rpm

-rw-r--r--   1 root root 22845941 May 30 13:05 Percona-Server-server-56-5.6.10-alpha60.2.324.rhel5.i686.rpm

drwxr-xr-x 14 root root     4096 May 30 16:51 ..

drwxr-xr-x 11 root root     4096 Jun   3 11:37 .

[root@localhost local]# rpm -ivh Percona-Server-shared-56-5.6.10-alpha60.2.324.rhel5.i686.rpm

warning: Percona-Server-shared-56-5.6.10-alpha60.2.324.rhel5.i686.rpm: Header V4 DSA signature: NOKEY, key ID cd2efd2a

Preparing...                 ########################################### [100%]

    1:Percona-Server-shared-5########################################### [100%]

[root@localhost local]# rpm -ivh Percona-Server-client-56-5.6.10-alpha60.2.324.rhel5.i686.rpm

warning: Percona-Server-client-56-5.6.10-alpha60.2.324.rhel5.i686.rpm: Header V4 DSA signature: NOKEY, key ID cd2efd2a

Preparing...                 ########################################### [100%]

    1:Percona-Server-client-5########################################### [100%]

[root@localhost local]# rpm -ivh Percona-Server-server-56-5.6.10-alpha60.2.324.rhel5.i686.rpm

warning: Percona-Server-server-56-5.6.10-alpha60.2.324.rhel5.i686.rpm: Header V4 DSA signature: NOKEY, key ID cd2efd2a

Preparing...                 ########################################### [100%]

    1:Percona-Server-server-5########################################### [100%]

2013-06-03 18:42:44 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2013-06-03 18:42:44 6302 [Note] InnoDB: The InnoDB memory heap is disabled

2013-06-03 18:42:44 6302 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2013-06-03 18:42:44 6302 [Note] InnoDB: Compressed tables use zlib 1.2.3

2013-06-03 18:42:44 6302 [Note] InnoDB: CPU does not support crc32 instructions

2013-06-03 18:42:44 6302 [Note] InnoDB: Using Linux native AIO

2013-06-03 18:42:44 6302 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2013-06-03 18:42:44 6302 [Note] InnoDB: Completed initialization of buffer pool

2013-06-03 18:42:47 6302 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!

2013-06-03 18:42:47 6302 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB

2013-06-03 18:42:47 6302 [Note] InnoDB: Database physically writes the file full: wait...

2013-06-03 18:42:51 6302 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB

2013-06-03 18:42:53 6302 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB

2013-06-03 18:42:54 6302 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

2013-06-03 18:42:54 6302 [Warning] InnoDB: New log files created, LSN=45781

2013-06-03 18:42:54 6302 [Note] InnoDB: Doublewrite buffer not found: creating new

2013-06-03 18:42:54 6302 [Note] InnoDB: Doublewrite buffer created

2013-06-03 18:42:54 6302 [Note] InnoDB: 128 rollback segment(s) are active.

2013-06-03 18:42:54 6302 [Warning] InnoDB: Creating foreign key constraint system tables.

2013-06-03 18:42:54 6302 [Note] InnoDB: Foreign key constraint system tables created

2013-06-03 18:42:54 6302 [Note] InnoDB: Creating tablespace and datafile system tables.

2013-06-03 18:42:54 6302 [Note] InnoDB: Tablespace and datafile system tables created.

2013-06-03 18:42:54 6302 [Note] InnoDB: Waiting for purge to start

2013-06-03 18:42:54 6302 [Note] InnoDB: 1.2.10 started; log sequence number 0

2013-06-03 18:42:54 6302 [Note] Binlog end

2013-06-03 18:42:55 6302 [Note] InnoDB: FTS optimize thread exiting.

2013-06-03 18:42:55 6302 [Note] InnoDB: Starting shutdown...

2013-06-03 18:42:56 6302 [Note] InnoDB: Shutdown completed; log sequence number 1625977

2013-06-03 18:42:56 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2013-06-03 18:42:56 6326 [Note] InnoDB: The InnoDB memory heap is disabled

2013-06-03 18:42:56 6326 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2013-06-03 18:42:56 6326 [Note] InnoDB: Compressed tables use zlib 1.2.3

2013-06-03 18:42:56 6326 [Note] InnoDB: CPU does not support crc32 instructions

2013-06-03 18:42:56 6326 [Note] InnoDB: Using Linux native AIO

2013-06-03 18:42:56 6326 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2013-06-03 18:42:56 6326 [Note] InnoDB: Completed initialization of buffer pool

2013-06-03 18:42:56 6326 [Note] InnoDB: Highest supported file format is Barracuda.

2013-06-03 18:42:56 6326 [Note] InnoDB: 128 rollback segment(s) are active.

2013-06-03 18:42:56 6326 [Note] InnoDB: Waiting for purge to start

2013-06-03 18:42:56 6326 [Note] InnoDB: 1.2.10 started; log sequence number 1625977

2013-06-03 18:42:56 6326 [Note] Binlog end

2013-06-03 18:42:56 6326 [Note] InnoDB: FTS optimize thread exiting.

2013-06-03 18:42:56 6326 [Note] InnoDB: Starting shutdown...

2013-06-03 18:42:59 6326 [Note] InnoDB: Shutdown completed; log sequence number 1625987

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

  /usr/bin/mysqladmin -u root password 'new-password'

  /usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:

  /usr/bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default.   This is

strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

WARNING: Found existing config file /usr/my.cnf on the system.

Because this file might be in use, it was not replaced,

but was used in bootstrap (unless you used --defaults-file)

and when you later start the server.

The new default config file was created as /usr/my-new.cnf,

please compare it with your file and take the changes you need.

WARNING: Default config file /etc/my.cnf exists on the system

This file will be read by default by the MySQL server

If you do not want to use this, either remove it, or use the

--defaults-file argument to mysqld_safe when starting the server

Percona Server is distributed with several useful UDF (User Defined Function) from Maatkit.

Run the following commands to create these functions:

mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"

mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"

mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

See http://code.google.com/p/maatkit/source/browse/trunk/udf for more details

步骤二：配置主从数据库参数配置文件

    master库

    cp/usr/share/mysql/my-default.cnf   /etc/my.cnf

    /etc/my.cnf中[mysqld]下一行添加

    explicit_defaults_for_timestamp=true

    log_bin       = mysql-bin

    server_id     = 111

[root@localhost ~]# vi /etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the

# *** default location during install, and will be replaced if you

# *** upgrade to a newer version of MySQL.

[mysqld]

explicit_defaults_for_timestamp=true

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

  innodb_buffer_pool_size = 128M

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

log_bin       = mysql-bin

server_id     = 111

# These are commonly set, remove the # and set as required.

# basedir = .....

# datadir = .....

# port = .....

# server_id = .....

# socket = .....

# 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

  sort_buffer_size = 2M

  read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

    slave库

    cp/usr/share/mysql/my-default.cnf   /etc/my.cnf

    /etc/my.cnf中 [mysqld]下一行添加

    explicit_defaults_for_timestamp=true

    log_bin           = mysql-bin

    server_id         = 112

    relay_log         = /var/lib/mysql/mysql-relay-bin

    log_slave_updates = 1

    read_only         =1

[root@localhost mysql]# vi /etc/my.cnf

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the

# *** default location during install, and will be replaced if you

# *** upgrade to a newer version of MySQL.

[mysqld]

explicit_defaults_for_timestamp=true

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

  innodb_buffer_pool_size = 128M

log_bin           = mysql-bin

server_id         = 112

relay_log         = /var/lib/mysql/mysql-relay-bin

log_slave_updates = 1

read_only         =1

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

# These are commonly set, remove the # and set as required.

# basedir = .....

# datadir = .....

# port = .....

# server_id = .....

# socket = .....

# 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

  sort_buffer_size = 2M

  read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

步骤三：启动主从数据库  

主数据库：

[root@localhost local]# service mysql start

Starting MySQL (Percona Server)......[   OK   ]

[root@localhost local]# netstat -nat

Active Internet connections (servers and established)

Proto Recv-Q Send-Q Local Address               Foreign Address             State      

tcp         0       0 127.0.0.1:2208               0.0.0.0:*                   LISTEN      

tcp         0       0 0.0.0.0:11111               0.0.0.0:*                   LISTEN      

tcp         0       0 0.0.0.0:111                 0.0.0.0:*                   LISTEN      

tcp         0       0 0.0.0.0:16851               0.0.0.0:*                   LISTEN      

tcp         0       0 192.168.122.1:53             0.0.0.0:*                   LISTEN      

tcp         0       0 127.0.0.1:631               0.0.0.0:*                   LISTEN      

tcp         0       0 127.0.0.1:25                 0.0.0.0:*                   LISTEN      

tcp         0       0 0.0.0.0:857                 0.0.0.0:*                   LISTEN      

tcp         0       0 127.0.0.1:2207               0.0.0.0:*                   LISTEN      

tcp         0       0 :::3306                     :::*                         LISTEN      

tcp         0       0 :::22                       :::*                         LISTEN      

tcp         0       0 ::ffff:192.168.66.111:22     ::ffff:192.168.66.101:1147   ESTABLISHED 

tcp         0       0 ::ffff:192.168.66.111:22     ::ffff:192.168.66.101:1852   ESTABLISHED 

[root@localhost mysql]# mysql -u root

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

Your MySQL connection id is 1

Server version: 5.6.10-56-log Percona Server (GPL), Release alpha60.2, Revision 324

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

--------------------

| Database           |

--------------------

| information_schema |

| mysql               |

| performance_schema |

| test               |

--------------------

4 rows in set (0.00 sec)

步骤四：主从数据库本别授权用户

主数据库：

mysql> show master status \G

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

              File: mysql-bin.000003

          Position: 120

      Binlog_Do_DB: 

  Binlog_Ignore_DB: 

Executed_Gtid_Set: 

1 row in set (0.06 sec)

mysql>   grant replication slave,replication client on *.* to repl@'192.168.66.%' identified by   'password';

Query OK, 0 rows affected (0.05 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status \G

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

              File: mysql-bin.000003

          Position: 349

      Binlog_Do_DB: 

  Binlog_Ignore_DB: 

Executed_Gtid_Set: 

1 row in set (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> show master status \G

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

              File: mysql-bin.000003

          Position: 349

      Binlog_Do_DB: 

  Binlog_Ignore_DB: 

Executed_Gtid_Set: 

1 row in set (0.00 sec)

从数据库：

[root@localhost ~]# mysql -u root

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

Your MySQL connection id is 1

Server version: 5.6.10-56-log Percona Server (GPL), Release alpha60.2, Revision 324

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>   grant replication slave,replication client on *.* to repl@'192.168.66.%' identified by   'password';

Query OK, 0 rows affected (0.05 sec)

mysql>     change master to master_host='192.168.66.111',master_user='repl',master_password='password',master_log_file='mysql-bin.000003',master_log_pos=0;

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

mysql> start slave;

Query OK, 0 rows affected (0.04 sec)

步骤五：检查主从数据库状态

mysql> show master status \G

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

              File: mysql-bin.000003

          Position: 349

      Binlog_Do_DB: 

  Binlog_Ignore_DB: 

Executed_Gtid_Set: 

1 row in set (0.00 sec)

mysql> 

[root@localhost ~]# mysql -u root

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

Your MySQL connection id is 1

Server version: 5.6.10-56-log Percona Server (GPL), Release alpha60.2, Revision 324

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>   grant replication slave,replication client on *.* to repl@'192.168.66.%' identified by   'password';

Query OK, 0 rows affected (0.05 sec)

mysql>     change master to master_host='192.168.66.111',master_user='repl',master_password='password',master_log_file='mysql-bin.000003',master_log_pos=0;

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

mysql> start slave;

Query OK, 0 rows affected (0.04 sec)

mysql> show slave status\G

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

                Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.66.111

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000003

          Read_Master_Log_Pos: 349

                Relay_Log_File: mysql-relay-bin.000002

                Relay_Log_Pos: 512

        Relay_Master_Log_File: mysql-bin.000003

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

              Relay_Log_Space: 685

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

                  Master_UUID: 7da898e2-cc3a-11e2-b143-000c29b8bea8

              Master_Info_File: /var/lib/mysql/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave 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.02 sec)

步骤五：测试：

        在数据库创建表并插入数据库，在从数据库查看

master库

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql

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> create table fml (name varchar(10),old numeric(8));

Query OK, 0 rows affected (0.08 sec)

mysql> insert into fml values('fml',30);

Query OK, 1 row affected (0.02 sec)

mysql> 

从库查看：

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql

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> select * from fhl;

Empty set (0.00 sec)

mysql> select * from fhl;

------ ------

| name | old   |

------ ------

| fhl   |   30 |

------ ------

1 row in set (0.00 sec)

mysql> 

卸载数据库：

[root@localhost local]# rpm -e Percona-Server-server-55-5.5.20-rel24.1.217.rhel5.i686

[root@localhost local]# rpm -e Percona-Server-client-55-5.5.20-rel24.1.217.rhel5.i686

[root@localhost local]# rpm -e Percona-Server-shared-55-5.5.20-rel24.1.217.rhel5.i686

[root@localhost local]# whereis mssql

mssql:

[root@localhost local]# whereis mysql

mysql: /usr/lib/mysql

[root@localhost local]# rm -fr /usr/lib/mysql

[root@localhost local]# find / -name mysql

/var/lib/mysql

/var/lib/mysql/mysql

[root@localhost local]# rm -fr /var/lib/mysql 

[root@localhost local]#