Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | 51CTO学院 | CSDN程序员研修院 | OSChina 博客 | 腾讯云社区 | 阿里云栖社区 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
知乎专栏多维度架构

第 1 章 MySQL Server

目录

1.1. MySQL 安装
1.1.1. Rocky Linux 9.0
1.1.2. CentOS
1.1.3. Docker
1.1.4. Ubuntu/Debian
1.1.5. 源码安装
1.1.6. 二进制版本
1.1.7. Installing MySQL on Linux Using the MySQL Yum Repository
1.1.8. Firewall
1.1.9. Mac OS
1.1.10. MariaDB
1.1.11. Percona
1.2. MySQL Plugin
1.2.1. validate_password
1.2.2. MySQL Images manager
1.2.3. MySQL fifo
1.2.4. 内容输出到文本插件
1.3. Replication
1.3.1. Master Slave
1.3.2. Master Master(主主)
1.3.3. Semisynchronous Replication
1.3.4. multi-master replication
1.3.5. multi-source replication
1.3.6. 与复制有关的问题
1.3.7. GTID
1.4. MySQL Custer
1.4.1. Management node (MGM node)
1.4.2. Data node
1.4.3. SQL node
1.4.4. Starting
1.4.5. Shutdown
1.4.6. Testing
1.5. MySQL Proxy
1.5.1. Ubuntu
1.5.2. CentOS
1.6. MySQL Router
1.6.1. 安装 MySQL Router
1.6.2. 配置 MySQL Router
1.6.3. MySQL Router , Haproxy,LVS 的选择
1.7. my.cnf
1.7.1. bind-address
1.7.2. 禁用TCP/IP链接
1.7.3. 配置字符集
1.7.4. 最大链接数 max_connections
1.7.5. 默认引擎 storage-engine
1.7.6. max_allowed_packet
1.7.7. skip-name-resolve
1.7.8. timeout
1.7.9. 与复制有关的参数
1.7.10. 与 InnoDB 有关的配置项
1.7.11. EVENT 设置
1.7.12. 日志
1.7.13. MySQL 5.7 my.cnf 实例
1.7.14. Example for my.cnf
1.8. variables
1.8.1. 查询多个变量
1.8.2. time_zone
1.8.3. sql_mode
1.8.4. wait_timeout
1.8.5. table_lock_wait_timeout
1.8.6. low_priority_updates
1.8.7. collation_server
1.8.8. character_set
1.8.9. datadir
1.8.10. plugin_dir
1.8.11. storage_engine
1.8.12. timeout
1.8.13. max_connections
1.8.14. 自动提交 autocommit
1.9. Monitoring
1.9.1. Analysis and Optimization
1.9.2. Cacti
1.9.3. Monitoring MySQL with SNMP

1.1. MySQL 安装

MySQL Installation

http://downloads.mysql.com/archives.php

1.1.1. Rocky Linux 9.0

		
[root@netkiller ~]# dnf install -y mysql-server

[root@netkiller ~]# systemctl enable mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
		
		

备份配置文件

			
cp /etc/my.cnf{,.original}
cp /etc/my.cnf.d/mysql-server.cnf{,.original}
cp /etc/my.cnf.d/client.cnf{,.original}

cat >> /etc/my.cnf.d/mysql-server.cnf <<EOF

# Add by Neo
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
explicit_defaults_for_timestamp=true
lower_case_table_names=1
EOF
			
		

设置文件打开数据

			
cat >> /etc/security/limits.d/20-nofile.conf <<EOF
mysql soft nofile 65535
mysql hard nofile 65535
EOF

mkdir /etc/systemd/system/mysqld.service.d/

cat >> /etc/systemd/system/mysqld.service.d/override.conf  <<EOF
[Service]
LimitNOFILE=65000
EOF
			
		

启动数据库

			
[root@netkiller ~]# systemctl start mysqld
			
		

创建用户数据库 root 用户

CREATE USER 'root'@'%' IDENTIFIED BY 'test';

GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;

			
[root@netkiller ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.28 Source distribution

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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> CREATE USER 'root'@'%' IDENTIFIED BY 'chen';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.02 sec)
			
		

开启防火墙

		
[root@netkiller ~]# firewall-cmd --permanent --zone=public --add-port=3306/tcp
success		
		
		

1.1.2. CentOS

1.1.2.1. CentOS 8 Stream + dnf 安装 Mysql

			
[root@localhost ~]# dnf install -y mysql-server mysql mysql-test
[root@localhost ~]# systemctl enable mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.	
			
			

备份配置文件

			
cp /etc/my.cnf{,.original}
cp /etc/my.cnf.d/mysql-server.cnf /etc/mysql-server.cnf.original
cat >> /etc/my.cnf.d/mysql-server.cnf <<EOF

# Add by Neo
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
explicit_defaults_for_timestamp=true
lower_case_table_names=1
EOF
			
			

设置文件打开数据

			
cat >> /etc/security/limits.d/20-nofile.conf <<EOF
mysql soft nofile 65535
mysql hard nofile 65535
EOF

mkdir /etc/systemd/system/mysqld.service.d/

cat >> /etc/systemd/system/mysqld.service.d/override.conf  <<EOF
[Service]
LimitNOFILE=65000
EOF
			
			

启动数据库

			
[root@localhost ~]# systemctl start mysqld			
			
			

创建用户

			
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.05 sec)

mysql> GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;	
Query OK, 0 rows affected (0.03 sec)
			
			

例 1.1. MySQL 8 创建root账号

				
[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2020, 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> CREATE USER 'root'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

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

mysql>
				
				

1.1.2.2. CentOS 6.2 + MySQL 5.5.25 (RPM)

准备下面的软件包


# ls -1
MySQL-client-5.5.25-1.el6.x86_64.rpm
MySQL-devel-5.5.25-1.el6.x86_64.rpm
MySQL-server-5.5.25-1.el6.x86_64.rpm
MySQL-shared-5.5.25-1.el6.x86_64.rpm
MySQL-shared-compat-5.5.25-1.el6.x86_64.rpm
			

使用 yum 本地安装 rpm, yum 可以帮你解决依赖于冲突


# yum localinstall MySQL-*
			

# /etc/init.d/mysql start
Starting MySQL... SUCCESS!

# /usr/bin/mysqladmin -u root password 'tUG26WSslP30bkbwtMhn'
			

1.1.2.3. MySQL 8.0

安装

			
curl -s https://raw.githubusercontent.com/oscm/shell/master/database/mysql/8.0/server.sh | bash
			
			

启动

			
systemctl enable mysql
systemctl start mysql
			
			

必须修改密码后才能使用

			
[root@netkiller ~]# grep "A temporary password" /var/log/mysqld.log
2018-04-03T02:24:16.935070Z 1 [Note] A temporary password is generated for root@localhost: kMA*d<e#Q3EC
2018-04-20T03:36:31.935143Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: MqatK=hae5F#

[root@netkiller ~]# mysqladmin -u root -p'MqatK=hae5F#' password
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
New password:
Confirm new password:
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

[root@netkiller ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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>

			
			

创建用户

			
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'MQiEge1ikst7S_6tlXzBOmt_4b';
Query OK, 0 rows affected (0.05 sec)

mysql> GRANT ALL ON *.* TO 'root'@'%';
Query OK, 0 rows affected (0.03 sec)
			
			

1.1.3. Docker

		
docker run --name mysql -d \
    --restart always \
    -e MYSQL_ROOT_PASSWORD=123456 \
    -e MYSQL_DATABASE=test \
    -e MYSQL_USER=test \
    -e MYSQL_PASSWORD=test \
    -p 127.0.0.1:3306:3306 \
    mysql:latest		
		
		

1.1.4. Ubuntu/Debian

Installation by apt under debian/ubuntu

安装环境 ubuntu 17.10

		
			sudo apt install mysql-server mysql-client
		
		

New password for the MySQL "root" user

		
┌──────────────────────┤ Configuring mysql-server-5.7 ├─────────────────────┐
│ While not mandatory, it is highly recommended that you set a password for the MySQL administrative "root" user.  │
│                                                                                                                  │
│ If that field is left blank, the password will not be changed.                                                   │
│                                                                                                                  │
│ New password for the MySQL "root" user:                                                                          │
│                                                                                                                  │
│ ****____________________________________________________________________________________________________________ │
│                                                                                                                  │
│                                                      <Ok>                                                        │
│                                                                                                                  │
└─────────────────────────────────────────────────────────────┘
		
		

Repeat password for the MySQL "root" user

		
         ┌───┤ Configuring mysql-server-5.7 ├────┐
         │                                             │
         │                                             │
         │ Repeat password for the MySQL "root" user:  │
         │                                             │
         │ ****_______________________________________ │
         │                                             │
         │                   <Ok>                      │
         │                                             │
         └─────────────────────────┘
         
		

尝试登录,验证是否安装成功

		
# mysql -udbuser -p
Enter password:

mysql> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*C6325DAF39AE6CC34E960D3C65F1398FE467E1D0' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

GRANT ALL PRIVILEGES ON example.* TO 'dbuser'@'localhost' IDENTIFIED BY '******' WITH GRANT OPTION;
FLUSH PRIVILEGES;
		
		

配置监听IP地址,默认数据库只能从 127.0.0.1访问

			neo@netkiller /etc/mysql/mysql.conf.d % vim
			/etc/mysql/mysql.conf.d/mysqld.cnf

			bind-address = 0.0.0.0
		

1.1.4.1. mysql-5.5.21-debian6.0-i686.deb

			
sudo apt-get install libaio1


sudo groupadd mysql
sudo useradd -r -g mysql mysql

sudo dpkg -i mysql-5.5.21-debian6.0-i686.deb

cd /opt/mysql/
sudo chown -R mysql .
sudo chgrp -R mysql .

cd server-5.5/

sudo support-files/binary-configure

sudo chown -R mysql data

# Next command is optional
shell> cp support-files/my-medium.cnf /etc/my.cnf

shell> bin/mysqld_safe --user=mysql &

# Next command is optional
sudo cp support-files/mysql.server /etc/init.d/mysql
			
			

1.1.5. 源码安装

Installation by source code

		
./configure \
--prefix=/usr/local/$MYSQL_DIR \
--enable-assembler \
--enable-local-infile \
--with-charset=utf8 \
--with-collation=utf8_general_ci \
--with-extra-charsets=none \
--with-openssl \
--with-pthread \
--with-unix-socket-path=/var/lib/mysql/mysql.sock \
--with-mysqld-user=mysql \
--with-mysqld-ldflags \
--with-client-ldflags \
--with-comment \
--with-big-tables \
--without-ndb-debug \
--without-docs \
--without-debug \
--without-bench

make && make install
		
		

/usr/local/$MYSQL_DIR/bin/mysql_install_db

other option

			--without-isam
			--without-innodb
			--without-ndbcluster
			--without-blackhole
			--without-ibmdb2i
			--without-federated
			--without-example
			--without-comment
			--localstatedir=/usr/local/mysql/data
		

1.1.6. 二进制版本

MySQL binary distribution

		
shell> groupadd mysql
shell> useradd -r -g mysql mysql
shell> cd /usr/local
shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz
shell> ln -s full-path-to-mysql-VERSION-OS mysql
shell> cd mysql
shell> chown -R mysql .
shell> chgrp -R mysql .
shell> scripts/mysql_install_db --user=mysql
shell> chown -R root .
shell> chown -R mysql data
# Next command is optional
shell> cp support-files/my-medium.cnf /etc/my.cnf
shell> bin/mysqld_safe --user=mysql &
# Next command is optional
shell> cp support-files/mysql.server /etc/init.d/mysql.server
		
		

install core database

		
[root@test mysql]# ./scripts/mysql_install_db
Installing MySQL system tables...
100428 23:16:20 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
OK
Filling help tables...
100428 23:16:20 [Warning] '--skip-locking' is deprecated and will be removed in a future release. Please use '--skip-external-locking' instead.
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

./bin/mysqladmin -u root password 'new-password'
./bin/mysqladmin -u root -h db.example.com password 'new-password'

Alternatively you can run:
./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.

You can start the MySQL daemon with:
cd . ; ./bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd ./mysql-test ; perl mysql-test-run.pl

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

set root's password

			[root@test mysql]# cp support-files/mysql.server
			/etc/init.d/mysqld
			[root@test mysql]# /etc/init.d/mysqld start
			Starting MySQL. [ OK ]

			[root@test mysql]# ./bin/mysqladmin -u root
			password 'chen'
			[root@test mysql]# ./bin/mysqladmin -u root -h
			db.example.com password 'chen'
		

test

		
[root@test mysql]# ./bin/mysql -uroot -pchen
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.45 MySQL Community Server (GPL)

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

mysql>
		
		

1.1.7. Installing MySQL on Linux Using the MySQL Yum Repository

1.1.7.1. MySQL 5.6

http://dev.mysql.com/doc/mysql-repo-excerpt/5.6/en/linux-installation-yum-repo.html


yum localinstall http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
			

安装MySQL Server


yum install mysql-server
chkconfig mysqld on
service mysqld start		
			

修改root密码


mysqladmin -u root password 'new-password'		
			

安全设置向导


/usr/bin/mysql_secure_installation		
			

1.1.7.2. MySQL 5.7

			
yum localinstall -y https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum install mysql-server -y
systemctl enable mysqld
systemctl start mysqld

cp /etc/my.cnf{,.original}

cat >> /etc/security/limits.d/20-nofile.conf <<EOF

mysql soft nofile 40960
mysql hard nofile 40960
EOF

cat >> /etc/my.cnf.d/default.cnf <<EOF
[mysqld]
skip-name-resolve
max_connections=8192
default-storage-engine=INNODB

#wait_timeout=30
#interactive_timeout=30

character-set-server=utf8
collation_server=utf8_general_ci
init_connect='SET NAMES utf8'

explicit_defaults_for_timestamp=true

query_cache_type=1
query_cache_size=512M

[client]
character_set_client=utf8

EOF
			
			

MySQL 5.7 会随机分配一个密码给用户


grep "A temporary password" /var/log/mysqld.log
			

登陆后修改密码


ALTER USER root@localhost identified by 'MQiEge1ikst7S_6tlXzBOmt_4b';
ALTER USER root@localhost PASSWORD EXPIRE NEVER;
			

1.1.8. Firewall

iptables

		
iptables -A INPUT -i eth0 -p tcp -s xxx.xxx.xxx.xxx --dport 3306 -j ACCEPT
		
		

1.1.9. Mac OS

		
brew install mysql
		
		

启动

		
brew services start mysql
		
		

1.1.10. MariaDB

http://mariadb.org/

1.1.10.1. CentOS 6 YUM 安装 MariaDB

		
cat >> /etc/yum.repos.d/MariaDB.repo <<EOF
# MariaDB 5.5 CentOS repository list - created 2013-12-04 02:17 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
		
		
# yum search MariaDB | grep MariaDB
============================= N/S Matched: MariaDB =============================
MariaDB-Galera-server.x86_64 : MariaDB: a very fast and robust SQL database
MariaDB-client.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-common.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-compat.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-devel.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-server.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-shared.x86_64 : MariaDB: a very fast and robust SQL database server
MariaDB-test.x86_64 : MariaDB: a very fast and robust SQL database server
		

安装数据库

# yum install -y MariaDB-server MariaDB-client
		

指定默认root密码

# mysqladmin -u root password 'chen'
		

数据库安全配置

# /usr/bin/mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n]
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
		

进入数据库

		
# mysql -uroot -pchen
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.34-MariaDB MariaDB Server

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> 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
MariaDB [mysql]>
		
		

使用上与MySQL并无差异

1.1.10.2. CentOS 7 安装 MariaDB

		
yum install -y mariadb		
		
		

1.1.11. Percona

http://www.percona.com/

1.1.11.1. Percona yum Repository

# yum install http://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-3.noarch.rpm
		

查看所有percona软件包

yum search percona
		

1.1.11.2. Percona XtraBackup

安装 XtraBackup

通过YUM安装 percona-xtrabackup

# yum install percona-xtrabackup
			

通过RPM安装 CentOS 6

http://www.percona.com/downloads/XtraBackup/LATEST/binary/redhat/6/x86_64/

# yum install -y http://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/redhat/6/x86_64/percona-xtrabackup-2.2.6-5042.el6.x86_64.rpm
			

通过RPM安装 CentOS 7

http://www.percona.com/downloads/XtraBackup/LATEST/binary/redhat/7/x86_64/

# yum install -y http://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/redhat/7/x86_64/percona-xtrabackup-2.2.6-5042.el7.x86_64.rpm
			

卸载

# yum remove percona-xtrabackup
			

查看文件列表

# rpm -ql percona-xtrabackup
/usr/bin/innobackupex
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-2.2.6
/usr/share/doc/percona-xtrabackup-2.2.6/COPYING
			
innobackupex

首先创建备份用户

			
mysql> CREATE USER 'backup'@'localhost' IDENTIFIED BY 's3cret';
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
mysql> FLUSH PRIVILEGES;
			
			
备份数据库

备份所有数据库

# mkdir -p /backup
# innobackupex --user=backup --password=chen /backup/full
				

备份指定数据库

# innobackupex --user=backup --password=chen --database=test /backup
				

--defaults-file=/etc/my.cnf 参数

# innobackupex --defaults-file=/etc/my.cnf --user=backup --password=chen --database=test /backup
				

备份后打包

# innobackupex --user=backup --password=chen --database=test --stream=tar /backup/  > test.tar
				

打包并压缩

# innobackupex --user=backup --password=chen --database=test --stream=tar /backup/ | gzip > test.tar.gz
				

备份到远程服务器

# innobackupex --user=backup --password=chen --defaults-file=/etc/my.cnf --database=test --stream=tar /backup | gzip | ssh neo@192.168.2.1 cat ">"   /backup/backup-2014-11-12.tar.gz
				

增量备份

# innobackupex --user=backup --password=chen --database=test /backup/incremental
# ls /backup/incremental
2014-11-12_13-45-26
# innobackupex --user=backup --password=chen --database=test --incremental --incremental-basedir=/backup/incremental/2014-11-12_13-45-26/ /backup/incremental
				
恢复数据库

恢复数据首先停止MySQL服务

# service mysql stop
				

恢复文件

# innobackupex --copy-back /path/to/BACKUP-DIR
# innobackupex --user=backup --password=chen --apply-log /backup/full/2014-11-12_13-45-26/
				

数据恢复完成后修改权限

$ chown -R mysql:mysql /var/lib/mysql
				

增量备份恢复方法

innobackupex --apply-log --redo-only BASE-DIR
innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1
innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-2
innobackupex --apply-log BASE-DIR
innobackupex --copy-back BASE-DIR
				
xbstream
			
$ innobackupex --stream=tar /tmp
$ innobackupex --stream=xbstream /root/backup/ > /root/backup/backup.xbstream
$ innobackupex --stream=xbstream --compress /root/backup/ > /root/backup/backup.xbstream

$ xbstream -x <  backup.xbstream -C /root/backup/
$ innobackupex --compress --stream=xbstream /root/backup/ | ssh user@otherhost "xbstream -x -C /root/backup/"
			
			
xtrabackup
# xtrabackup --user=backup --password=chen --backup --target-dir=/backup/backup
			

1.1.11.3. Percona Toolkit - MySQL Management Software

YUM安装

# yum install -y percona-toolkit
		

RPM安装

# yum install -y http://www.percona.com/redir/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.11-1.noarch.rpm
		

percona-toolkit 所含的文件

# rpm -ql percona-toolkit
/usr/bin/pt-align
/usr/bin/pt-archiver
/usr/bin/pt-config-diff
/usr/bin/pt-deadlock-logger
/usr/bin/pt-diskstats
/usr/bin/pt-duplicate-key-checker
/usr/bin/pt-fifo-split
/usr/bin/pt-find
/usr/bin/pt-fingerprint
/usr/bin/pt-fk-error-logger
/usr/bin/pt-heartbeat
/usr/bin/pt-index-usage
/usr/bin/pt-ioprofile
/usr/bin/pt-kill
/usr/bin/pt-mext
/usr/bin/pt-mysql-summary
/usr/bin/pt-online-schema-change
/usr/bin/pt-pmp
/usr/bin/pt-query-digest
/usr/bin/pt-show-grants
/usr/bin/pt-sift
/usr/bin/pt-slave-delay
/usr/bin/pt-slave-find
/usr/bin/pt-slave-restart
/usr/bin/pt-stalk
/usr/bin/pt-summary
/usr/bin/pt-table-checksum
/usr/bin/pt-table-sync
/usr/bin/pt-table-usage
/usr/bin/pt-upgrade
/usr/bin/pt-variable-advisor
/usr/bin/pt-visual-explain
/usr/share/doc/percona-toolkit-2.2.11
/usr/share/doc/percona-toolkit-2.2.11/COPYING
/usr/share/doc/percona-toolkit-2.2.11/Changelog
/usr/share/doc/percona-toolkit-2.2.11/INSTALL
/usr/share/doc/percona-toolkit-2.2.11/README
/usr/share/man/man1/percona-toolkit.1p.gz
/usr/share/man/man1/pt-align.1p.gz
/usr/share/man/man1/pt-archiver.1p.gz
/usr/share/man/man1/pt-config-diff.1p.gz
/usr/share/man/man1/pt-deadlock-logger.1p.gz
/usr/share/man/man1/pt-diskstats.1p.gz
/usr/share/man/man1/pt-duplicate-key-checker.1p.gz
/usr/share/man/man1/pt-fifo-split.1p.gz
/usr/share/man/man1/pt-find.1p.gz
/usr/share/man/man1/pt-fingerprint.1p.gz
/usr/share/man/man1/pt-fk-error-logger.1p.gz
/usr/share/man/man1/pt-heartbeat.1p.gz
/usr/share/man/man1/pt-index-usage.1p.gz
/usr/share/man/man1/pt-ioprofile.1p.gz
/usr/share/man/man1/pt-kill.1p.gz
/usr/share/man/man1/pt-mext.1p.gz
/usr/share/man/man1/pt-mysql-summary.1p.gz
/usr/share/man/man1/pt-online-schema-change.1p.gz
/usr/share/man/man1/pt-pmp.1p.gz
/usr/share/man/man1/pt-query-digest.1p.gz
/usr/share/man/man1/pt-show-grants.1p.gz
/usr/share/man/man1/pt-sift.1p.gz
/usr/share/man/man1/pt-slave-delay.1p.gz
/usr/share/man/man1/pt-slave-find.1p.gz
/usr/share/man/man1/pt-slave-restart.1p.gz
/usr/share/man/man1/pt-stalk.1p.gz
/usr/share/man/man1/pt-summary.1p.gz
/usr/share/man/man1/pt-table-checksum.1p.gz
/usr/share/man/man1/pt-table-sync.1p.gz
/usr/share/man/man1/pt-table-usage.1p.gz
/usr/share/man/man1/pt-upgrade.1p.gz
/usr/share/man/man1/pt-variable-advisor.1p.gz
/usr/share/man/man1/pt-visual-explain.1p.gz