本文以在最新的percona5.6.10版本在虚拟机上进行安装配置,给第一次接触percona一个手把手教的过程。以下内容,全部来自实际测试环境。
环境:
两个ip:
安装步骤和思路:
1.主从库安装及配置文件配置
2.主库和备库都需要建立账号:
3.slave启动复制:
4. 检查状态
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%]
[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%]
[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%]
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:
Alternatively you can run:
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
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
步骤二:配置主从数据库参数配置文件
[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%.
# 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.
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
[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%.
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.
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 ***************************
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 ***************************
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 ***************************
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 ***************************
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 ***************************
Master_SSL_Verify_Server_Cert: No
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]#