| 知乎专栏 |
目录
http://downloads.mysql.com/archives.php
[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 redis soft nofile 65535 redis 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) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
开启防火墙
[root@netkiller ~]# firewall-cmd --permanent --zone=public --add-port=3306/tcp success
[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>
准备下面的软件包
# 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'
安装
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)
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
注意生产环境不要使用 MYSQL_ROOT_PASSWORD，MYSQL_ROOT_PASSWORD 会泄漏密码
[root@netkiller mysql]# docker exec -it mysql env PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin HOSTNAME=88c404ee4ec2 TERM=xterm MYSQL_ROOT_PASSWORD=0FA8F23DB326 MYSQL_DATABASE=test MYSQL_USER=test MYSQL_PASSWORD=test TZ=Asia/Shanghai GOSU_VERSION=1.16 MYSQL_MAJOR=8.0 MYSQL_VERSION=8.0.34-1.el8 MYSQL_SHELL_VERSION=8.0.34-1.el8 HOME=/root
ubuntu@VM-0-13-ubuntu:~$ sudo apt install mysql-server
查看端口
ubuntu@VM-0-13-ubuntu:~$ ss -lnt | grep 3306 LISTEN 0 70 127.0.0.1:33060 0.0.0.0:* LISTEN 0 151 127.0.0.1:3306 0.0.0.0:*
查看密码
ubuntu@VM-0-13-ubuntu:~$ sudo cat /etc/mysql/debian.cnf # Automatically generated for Debian scripts. DO NOT TOUCH! [client] host = localhost user = debian-sys-maint password = DAl49JLNkjCHo9ib socket = /var/run/mysqld/mysqld.sock [mysql_upgrade] host = localhost user = debian-sys-maint password = DAl49JLNkjCHo9ib socket = /var/run/mysqld/mysqld.sock
测试登陆
ubuntu@VM-0-13-ubuntu:~$ mysql -h localhost -u debian-sys-maint -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 18 Server version: 8.0.40-0ubuntu0.20.04.1 (Ubuntu) Copyright (c) 2000, 2024, 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>
重制 root 密码
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf 加入 skip-grant-tables
重启 mysql，然后运行下面SQL修改本地 root 密码
use mysql; flush privileges; alter user 'root'@'localhost' identified with mysql_native_password by 'MQt7SOmtiEge6tlXzB1iks4b';
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> alter user 'root'@'localhost' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where user='root' \G
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher: 0x
x509_issuer: 0x
x509_subject: 0x
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
password_expired: N
password_last_changed: 2025-01-06 16:50:36
password_lifetime: NULL
account_locked: N
Create_role_priv: Y
Drop_role_priv: Y
Password_reuse_history: NULL
Password_reuse_time: NULL
Password_require_current: NULL
User_attributes: NULL
1 row in set (0.00 sec)
创建外部访问 root 用户
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'lXzB1it7SOmtiMQEge6tks4b'; 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)
修改配置文件，移除 skip-grant-tables 增加下面配置项,然后重启
bind-address = 0.0.0.0
安装环境 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
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
./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
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>
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
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;
http://mariadb.org/
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并无差异
http://www.percona.com/
# yum install http://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-3.noarch.rpm
查看所有percona软件包
yum search percona
通过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
首先创建备份用户
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
$ 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/"
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