第 1 章 MySQL Server-阿里云开发者社区

1.1. MySQL Installation

1.1.1. Installation by apt-get under debian/ubuntu

1.1.2. Installation by source code

1.1.3. MySQL binary distribution

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

1.1.5. CentOS 6.2 + MySQL 5.5.25 (RPM)

1.1.6. mysql-admin

1.1.7. Installing MySQL on Linux Using the MySQL Yum Repository

1.1.7.1. MySQL 5.6
1.1.7.2. MySQL 5.7

1.1.8. Mac

1.1.9. Firewall

1.1.10. Limit 状态

1.1.11. 使用 Btrfs 文件系统存储mysql数据

1.1.12. Mac OS

1.2. MariaDB

1.2.1. CentOS YUM 安装 MariaDB
1.2.2. MariaDB Audit Plugin

1.3. Percona

1.3.1. Percona yum Repository

1.3.2. Percona XtraBackup

1.3.2.1. 安装 XtraBackup

1.3.2.2. innobackupex

1.3.2.2.1. 备份数据库
1.3.2.2.2. 恢复数据库

1.3.2.3. xbstream

1.3.2.4. xtrabackup

1.3.3. Percona Toolkit - MySQL Management Software

1.4. my.cnf

1.4.1. bind-address

1.4.2. 禁用TCP/IP链接

1.4.3. 配置字符集

1.4.4. 最大链接数 max_connections

1.4.5. 默认引擎 storage-engine

1.4.6. max_allowed_packet

1.4.7. skip-name-resolve

1.4.8. timeout

1.4.9. 与复制有关的参数

1.4.9.1. 用于主库的选项 Master
1.4.9.2. 用于从库的选项 Slave
1.4.9.3. 逃过错误

1.4.10. 与 InnoDB 有关的配置项

1.4.11. EVENT 设置

1.4.12. 日志

1.4.13. MySQL 5.7 my.cnf 实例

1.4.14. Example for my.cnf

1.5. MySQL Plugin

1.5.1. validate_password
1.5.2. MySQL Images manager
1.5.3. MySQL fifo
1.5.4. 内容输出到文本插件

1.6. Replication

1.6.1. Master Slave

1.6.1.1. Master
1.6.1.2. Slave
1.6.1.3. Testing
1.6.1.4. 将现有数据库迁移到主从结构数据库
1.6.1.5. 主从复制安全问题

1.6.2. Master Master(主主)

1.6.2.1. Master A
1.6.2.2. Master B
1.6.2.3. 将Master A 数据库同步到 Master B 两端数据库内容保持一致
1.6.2.4. Master A - B 同步两端数据库
1.6.2.5. Master A 数据库解除只读权限
1.6.2.6. 查看主主的工作状态

1.6.3. Semisynchronous Replication

1.6.3.1. Master
1.6.3.2. Slave 配置
1.6.3.3. 卸载插件
1.6.3.4. my.cnf

1.6.4. multi-master replication

1.6.5. multi-source replication

1.6.6. 与复制有关的问题

1.6.6.1. 主从不同步问题
1.6.6.2. mysql-bin 清理问题
1.6.6.3. 跳过 Last_Errno
1.6.6.4. 重置Slave

1.6.7. GTID

1.6.7.1. Master
1.6.7.2. Slave

1.7. MySQL Custer

1.7.1. Management node (MGM node)
1.7.2. Data node
1.7.3. SQL node
1.7.4. Starting
1.7.5. Shutdown
1.7.6. Testing

1.8. MySQL Proxy

1.8.1. Ubuntu

1.8.2. CentOS

1.8.2.1. FAQ

1.9. MySQL Router

1.9.1. 安装 MySQL Router

1.9.2. 配置 MySQL Router

1.9.2.1. 主备配置
1.9.2.2. 负载均衡配置

1.9.3. MySQL Router ， Haproxy，LVS 的选择

1.10. SHOW COMMAND

1.10.1. 查看版本

1.10.2. status

1.10.2.1. show status
1.10.2.2. show master status
1.10.2.3. show slave status
1.10.2.4. show plugins

1.10.3. show processlist

1.10.4. variables

1.10.4.1. time_zone

1.10.4.2. sql_mode

1.10.4.2.1. 设置 sql_mode
1.10.4.2.2. 查看 sql_mode
1.10.4.2.3. 兼容早起 MySQL 版本
1.10.4.2.4. 5.7.16

1.10.4.3. wait_timeout

1.10.4.4. table_lock_wait_timeout

1.10.4.5. low_priority_updates

1.10.4.6. character_set

1.10.4.7. datadir

1.10.4.8. plugin_dir

1.10.4.9. storage_engine

1.10.4.10. timeout

1.10.4.11. max_connections

1.10.5. binary 日志

1.10.6. 线程的使用情况

1.10.7. DATABASES

1.10.8. TABLE

1.10.9. 临时表

1.10.10. 排序统计信息

1.10.11. Key 状态

1.10.12. FUNCTION

1.10.13. PROCEDURE

1.10.14. TRIGGERS

1.10.15. EVENTS

1.10.16. 引擎(ENGINES)

1.10.17. 字符集(Collation)

1.10.18. SHOW GRANTS

1.11. Monitoring

1.11.1. Analysis and Optimization

1.11.1.1. mytop - top like query monitor for MySQL
1.11.1.2. mtop - MySQL terminal based query monitor
1.11.1.3. innotop
1.11.1.4. mysqlreport - A friendly report of important MySQL status values
1.11.1.5. mysqltuner - MySQL configuration assistant

1.11.2. Munin

1.11.3. Cacti

1.11.4. Monitoring MySQL with SNMP

1.1. MySQL Installation

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

1.1.1. Installation by apt-get under debian/ubuntu

安装环境 ubuntu 8.10

sudo apt-get install mysql-server

New password for the MySQL "root" user

		
         ┌──────────────────────┤ Configuring mysql-server-5.0 ├─────────────────────┐
         │ 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.0 ├────┐
         │                                             │
         │                                             │
         │ Repeat password for the MySQL "root" user:  │
         │                                             │
         │ ****_______________________________________ │
         │                                             │
         │                   <Ok>                      │
         │                                             │
         └─────────────────────────┘

create database

		
create database example;

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;

./mysql -udbuser -p
Enter password:

./mysql -udbuser -p example < /tmp/example_china_copy.sql

./mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use example;
Database changed
mysql> show tables;

配置文件样本

debian:~# ls /usr/share/doc/mysql-server-5.0/examples/
my-huge.cnf.gz  my-innodb-heavy-4G.cnf.gz  my-large.cnf.gz  my-medium.cnf.gz  my-small.cnf  ndb_mgmd.cnf

1.1.2. 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.3. 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.4. 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. 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.6. mysql-admin

$ sudo apt-get install mysql-admin

运行mysql-admin

/usr/bin/mysql-admin

运行 mysql-query-browser

mysql-query-browser --query="SELECT * FROM users"

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. Mac

安装

brew install mysql

启动

brew services start mysql

1.1.9. Firewall

iptables

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

1.1.10. Limit 状态

$ sudo cat /proc/`pidof mysqld`/limits
Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            10485760             unlimited            bytes     
Max core file size        0                    unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             62662                62662                processes 
Max open files            20480                20480                files     
Max locked memory         65536                65536                bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       62662                62662                signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us

1.1.11. 使用 Btrfs 文件系统存储mysql数据

		
#!/bin/sh
systemctl stop mysqld

btrfs subvolume create /srv/@mysql
btrfs subvolume list /srv/

UUID=$(blkid | grep btrfs | sed -e 's/.*UUID="\([^"]*\)".*/\1/')
# UUID=786f570d-fe5c-4d5f-832a-c1b0963dd4e6 /srv btrfs defaults 1 1
cat << EOF >> /etc/fstab
UUID=${UUID} /var/lib/mysql  btrfs   noatime,nodiratime,subvol=@mysql 0 2
EOF

mkdir /tmp/mysql
mv /var/lib/mysql/* /tmp/mysql/

mount /var/lib/mysql/
chown mysql:mysql /var/lib/mysql

mv /tmp/mysql/* /var/lib/mysql/

systemctl start mysqld

1.1.12. Mac OS

brew install mysql

启动

brew services start mysql

原文出处：Netkiller 系列手札
本文作者：陈景峯
转载请与作者联系，同时请务必标明文章原始出处和作者信息及本声明。

第 1 章 MySQL Server