mysql之percona5.6.10masterslave…
来源：互联网 发布：windows oracle主备 编辑：程序博客网 时间：2024/06/03 13:25
本文以在最新的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:47Percona-Server-shared-55-5.5.20-rel24.1.217.rhel5.i686.rpm
-rw-r--r-- 1 root root 8597327 May 29 14:56Percona-Server-client-55-5.5.20-rel24.1.217.rhel5.i686.rpm
-rw-r--r-- 1 root root 21906622 May 29 15:05Percona-Server-server-55-5.5.20-rel24.1.217.rhel5.i686.rpm
-rw-r--r-- 1 root root 1077782 May 29 17:57Percona-Server-shared-56-5.6.10-alpha60.2.324.rhel5.i686.rpm
-rw-r--r-- 1 root root 10247953 May 29 18:04Percona-Server-client-56-5.6.10-alpha60.2.324.rhel5.i686.rpm
-rw-r--r-- 1 root root 22845941 May 30 13:05Percona-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 -ivhPercona-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 -ivhPercona-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 -ivhPercona-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 implicitDEFAULT value is deprecated. Please use--explicit_defaults_for_timestamp server option (see documentationfor more details).
2013-06-03 18:42:44 6302 [Note] InnoDB: The InnoDB memory heapis disabled
2013-06-03 18:42:44 6302 [Note] InnoDB: Mutexes and rw_locksuse GCC atomic builtins
2013-06-03 18:42:44 6302 [Note] InnoDB: Compressed tables usezlib 1.2.3
2013-06-03 18:42:44 6302 [Note] InnoDB: CPU does not supportcrc32 instructions
2013-06-03 18:42:44 6302 [Note] InnoDB: Using Linux nativeAIO
2013-06-03 18:42:44 6302 [Note] InnoDB: Initializing bufferpool, size = 128.0M
2013-06-03 18:42:44 6302 [Note] InnoDB: Completedinitialization of buffer pool
2013-06-03 18:42:47 6302 [Note] InnoDB: The first specifieddata file ./ibdata1 did not exist: a new database to becreated!
2013-06-03 18:42:47 6302 [Note] InnoDB: Setting file ./ibdata1size to 12 MB
2013-06-03 18:42:47 6302 [Note] InnoDB: Database physicallywrites 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 filescreated, LSN=45781
2013-06-03 18:42:54 6302 [Note] InnoDB: Doublewrite buffer notfound: creating new
2013-06-03 18:42:54 6302 [Note] InnoDB: Doublewrite buffercreated
2013-06-03 18:42:54 6302 [Note] InnoDB: 128 rollbacksegment(s) are active.
2013-06-03 18:42:54 6302 [Warning] InnoDB: Creating foreignkey constraint system tables.
2013-06-03 18:42:54 6302 [Note] InnoDB: Foreign key constraintsystem tables created
2013-06-03 18:42:54 6302 [Note] InnoDB: Creating tablespaceand datafile system tables.
2013-06-03 18:42:54 6302 [Note] InnoDB: Tablespace anddatafile system tables created.
2013-06-03 18:42:54 6302 [Note] InnoDB: Waiting for purge tostart
2013-06-03 18:42:54 6302 [Note] InnoDB: 1.2.10 started; logsequence number 0
2013-06-03 18:42:54 6302 [Note] Binlog end
2013-06-03 18:42:55 6302 [Note] InnoDB: FTS optimize threadexiting.
2013-06-03 18:42:55 6302 [Note] InnoDB: Startingshutdown...
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 implicitDEFAULT value is deprecated. Please use--explicit_defaults_for_timestamp server option (see documentationfor more details).
2013-06-03 18:42:56 6326 [Note] InnoDB: The InnoDB memory heapis disabled
2013-06-03 18:42:56 6326 [Note] InnoDB: Mutexes and rw_locksuse GCC atomic builtins
2013-06-03 18:42:56 6326 [Note] InnoDB: Compressed tables usezlib 1.2.3
2013-06-03 18:42:56 6326 [Note] InnoDB: CPU does not supportcrc32 instructions
2013-06-03 18:42:56 6326 [Note] InnoDB: Using Linux nativeAIO
2013-06-03 18:42:56 6326 [Note] InnoDB: Initializing bufferpool, size = 128.0M
2013-06-03 18:42:56 6326 [Note] InnoDB: Completedinitialization of buffer pool
2013-06-03 18:42:56 6326 [Note] InnoDB: Highest supported fileformat is Barracuda.
2013-06-03 18:42:56 6326 [Note] InnoDB: 128 rollbacksegment(s) are active.
2013-06-03 18:42:56 6326 [Note] InnoDB: Waiting for purge tostart
2013-06-03 18:42:56 6326 [Note] InnoDB: 1.2.10 started; logsequence number 1625977
2013-06-03 18:42:56 6326 [Note] Binlog end
2013-06-03 18:42:56 6326 [Note] InnoDB: FTS optimize threadexiting.
2013-06-03 18:42:56 6326 [Note] InnoDB: Startingshutdown...
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 followingcommands:
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/mysqlbugscript!
The latest information about MySQL is available on the webat
Support MySQL by buying support/licenses athttp://shop.mysql.com
WARNING: Found existing config file /usr/my.cnf on thesystem.
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 youneed.
WARNING: Default config file /etc/my.cnf exists on thesystem
This file will be read by default by the MySQL server
If you do not want to use this, either remove it, or usethe
--defaults-file argument to mysqld_safe when starting theserver
Percona Server is distributed with several useful UDF (UserDefined 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/udffor 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 becopied to the
# *** default location during install, and will be replaced ifyou
# *** upgrade to a newer version of MySQL.
[mysqld]
explicit_defaults_for_timestamp=true
# Remove leading # and set to the amount of RAM for the mostimportant data
# cache in MySQL. Start at 70% of total RAM for dedicatedserver, else 10%.
# Remove leading # to turn on a very important data integrityoption: 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 asrequired.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reportingservers.
# The server defaults are faster for transactions and fastSELECTs.
# Adjust sizes as needed, experiment to find the optimalvalues.
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 becopied to the
# *** default location during install, and will be replaced ifyou
# *** upgrade to a newer version of MySQL.
[mysqld]
explicit_defaults_for_timestamp=true
# Remove leading # and set to the amount of RAM for the mostimportant data
# cache in MySQL. Start at 70% of total RAM for dedicatedserver, 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 integrityoption: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set asrequired.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# Remove leading # to set options mainly useful for reportingservers.
# The server defaults are faster for transactions and fastSELECTs.
# Adjust sizes as needed, experiment to find the optimalvalues.
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 ForeignAddress 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 endwith ; or \g.
Your MySQL connection id is 1
Server version: 5.6.10-56-log Percona Server (GPL), Releasealpha60.2, Revision 324
Copyright (c) 2000, 2013, Oracle and/or its affiliates. Allrights reserved.
Oracle is a registered trademark of Oracle Corporation and/orits
affiliates. Other names may be trademarks of theirrespective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the currentinput 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 replicationslave,replication client on *.* to repl@'192.168.66.%' identifiedby '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 endwith ; or \g.
Your MySQL connection id is 1
Server version: 5.6.10-56-log Percona Server (GPL), Releasealpha60.2, Revision 324
Copyright (c) 2000, 2013, Oracle and/or its affiliates. Allrights reserved.
Oracle is a registered trademark of Oracle Corporation and/orits
affiliates. Other names may be trademarks of theirrespective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the currentinput statement.
mysql> grant replicationslave,replication client on *.* to repl@'192.168.66.%' identifiedby 'password';
Query OK, 0 rows affected (0.05 sec)
mysql> change master tomaster_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 endwith ; or \g.
Your MySQL connection id is 1
Server version: 5.6.10-56-log Percona Server (GPL), Releasealpha60.2, Revision 324
Copyright (c) 2000, 2013, Oracle and/or its affiliates. Allrights reserved.
Oracle is a registered trademark of Oracle Corporation and/orits
affiliates. Other names may be trademarks of theirrespective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the currentinput statement.
mysql> grant replicationslave,replication client on *.* to repl@'192.168.66.%' identifiedby 'password';
Query OK, 0 rows affected (0.05 sec)
mysql> change master tomaster_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 currentinput statement.
mysql> use mysql
Reading table information for completion of table and columnnames
You can turn off this feature to get a quicker startup with-A
Database changed
mysql> create table fml (name varchar(10),oldnumeric(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 currentinput statement.
mysql> use mysql
Reading table information for completion of table and columnnames
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 -ePercona-Server-server-55-5.5.20-rel24.1.217.rhel5.i686
[root@localhost local]# rpm -ePercona-Server-client-55-5.5.20-rel24.1.217.rhel5.i686
[root@localhost local]# rpm -ePercona-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]#
0 0
- mysql之percona5.6.10masterslave…
- freebsd下安装mysql（percona5.7）
- CentOS6.0上面安装Percona5.5版MySQL
- activemq cluster masterslave 配置
- Percona5.5新参数：innodb_fake_changes
- percona5.5.16安装sphinx存储引擎
- 由percona5.5参数innodb_adaptive_flushing_method想到的....
- percona5.26基于gtid的主从复制配置
- percona5.6中对索引的优化-实践1
- 同步测试---percona5.6.37-83.0同步数据到mysql5.5.47官方版本
- MySQL之初步MySQL
- 【MySql】MySql之安装
- 【MySql】MySql之管理
- 【MySql】MySql之语句
- 【MySql】MySql之事务
- 【MySql】MySql之索引
- 【MySql】MySql之序列
- MySQL学习之MySQL引擎
- mysql 导出表结构操作
- Vm启动95%hang住，无法继续
- win7 下如何完全卸载oracle 10g
- win7下 Plsql/toad无法连接or…
- sysbench工具手把手安装配置及使用…
- mysql之percona5.6.10masterslave…
- Bigint Multiplication：大数乘法（hihoCoder C++）
- Another app is cu…
- prvf-0040 the remote&n…
- Starting httpd: httpd: Couldnot reliably determine the server's fully qualified domainname
- Oracle12c日常pdb创建、开关和删除…
- std::string的substr越界问题
- 第一博
- Linux Error: 11: Resource temporarily unavailable
