开发者社区> 玄学酱> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

第 19 章 MySQL Server

简介:
+关注继续查看

目录

19.1. MySQL Installation
19.1.1. Installation by apt-get under debian/ubuntu
19.1.2. Installation by source code
19.1.3. MySQL binary distribution
19.1.4. mysql-5.5.21-debian6.0-i686.deb
19.1.5. CentOS 6.2 + MySQL 5.5.25 (RPM)
19.1.6. mysql-admin
19.1.7. Installing MySQL on Linux Using the MySQL Yum Repository
19.1.7.1. MySQL 5.6
19.1.7.2. MySQL 5.7
19.1.8. Mac
19.1.9. Firewall
19.1.10. Limit 状态
19.1.11. 使用 Btrfs 文件系统存储mysql数据
19.1.12. Mac OS
19.2. MariaDB
19.2.1. CentOS YUM 安装 MariaDB
19.2.2. MariaDB Audit Plugin
19.3. Percona
19.3.1. Percona yum Repository
19.3.2. Percona XtraBackup
19.3.2.1. 安装 XtraBackup
19.3.2.2. innobackupex
19.3.2.2.1. 备份数据库
19.3.2.2.2. 恢复数据库
19.3.2.3. xbstream
19.3.2.4. xtrabackup
19.3.3. Percona Toolkit - MySQL Management Software
19.4. my.cnf
19.4.1. bind-address
19.4.2. 禁用TCP/IP链接
19.4.3. 配置字符集
19.4.4. 最大链接数 max_connections
19.4.5. 默认引擎 storage-engine
19.4.6. max_allowed_packet
19.4.7. skip-name-resolve
19.4.8. timeout
19.4.9. 与复制有关的参数
19.4.9.1. 用于主库的选项 Master
19.4.9.2. 用于从库的选项 Slave
19.4.9.3. 逃过错误
19.4.10. 与 InnoDB 有关的配置项
19.4.11. EVENT 设置
19.4.12. 日志
19.4.13. MySQL 5.7 my.cnf 实例
19.4.14. Example for my.cnf
19.5. MySQL Plugin
19.5.1. validate_password
19.5.2. MySQL Images manager
19.5.3. MySQL fifo
19.5.4. 内容输出到文本插件
19.6. Replication
19.6.1. Master Slave
19.6.1.1. Master
19.6.1.2. Slave
19.6.1.3. Testing
19.6.1.4. 将现有数据库迁移到主从结构数据库
19.6.1.5. 主从复制安全问题
19.6.2. Master Master(主主)
19.6.2.1. Master A
19.6.2.2. Master B
19.6.2.3. 将Master A 数据库 同步到 Master B 两端数据库内容保持一致
19.6.2.4. Master A - B 同步两端数据库
19.6.2.5. Master A 数据库解除只读权限
19.6.2.6. 查看主主的工作状态
19.6.3. Semisynchronous Replication
19.6.3.1. Master
19.6.3.2. Slave 配置
19.6.3.3. 卸载插件
19.6.3.4. my.cnf
19.6.4. multi-master replication
19.6.5. multi-source replication
19.6.6. 与复制有关的问题
19.6.6.1. 主从不同步问题
19.6.6.2. mysql-bin 清理问题
19.6.6.3. 跳过 Last_Errno
19.6.6.4. 重置Slave
19.6.7. GTID
19.6.7.1. Master
19.6.7.2. Slave
19.7. MySQL Custer
19.7.1. Management node (MGM node)
19.7.2. Data node
19.7.3. SQL node
19.7.4. Starting
19.7.5. Shutdown
19.7.6. Testing
19.8. MySQL Proxy
19.8.1. Ubuntu
19.8.2. CentOS
19.8.2.1. FAQ
19.9. MySQL Router
19.9.1. 安装 MySQL Router
19.9.2. 配置 MySQL Router
19.9.2.1. 主备配置
19.9.2.2. 负载均衡配置
19.9.3. MySQL Router , Haproxy,LVS 的选择
19.10. variables
19.10.1. time_zone
19.10.2. sql_mode
19.10.2.1. 设置 sql_mode
19.10.2.2. 查看 sql_mode
19.10.2.3. 兼容早起 MySQL 版本
19.10.2.4. 5.7.16
19.10.3. wait_timeout
19.10.4. table_lock_wait_timeout
19.10.5. low_priority_updates
19.10.6. character_set
19.10.7. datadir
19.10.8. plugin_dir
19.10.9. storage_engine
19.10.10. timeout
19.10.11. max_connections
19.11. SHOW COMMAND
19.11.1. 查看版本
19.11.2. status
19.11.2.1. show status
19.11.2.2. show master status
19.11.2.3. show slave status
19.11.2.4. show plugins
19.11.3. show processlist
19.11.4. binary 日志
19.11.5. 线程的使用情况
19.11.6. DATABASES
19.11.7. TABLE
19.11.8. 临时表
19.11.9. 排序统计信息
19.11.10. Key 状态
19.11.11. FUNCTION
19.11.12. PROCEDURE
19.11.13. TRIGGERS
19.11.14. EVENTS
19.11.15. 引擎(ENGINES)
19.11.16. 字符集(Collation)
19.11.17. SHOW GRANTS
19.12. Monitoring
19.12.1. Analysis and Optimization
19.12.1.1. mytop - top like query monitor for MySQL
19.12.1.2. mtop - MySQL terminal based query monitor
19.12.1.3. innotop
19.12.1.4. mysqlreport - A friendly report of important MySQL status values
19.12.1.5. mysqltuner - MySQL configuration assistant
19.12.2. Munin
19.12.3. Cacti
19.12.4. Monitoring MySQL with SNMP

19.1. MySQL Installation

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

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

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

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

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

19.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'
		

19.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"
		

19.1.7. Installing MySQL on Linux Using the MySQL Yum Repository

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

19.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;
			

19.1.8. Mac

安装

brew install mysql	
		

启动

brew services start mysql		
		

19.1.9. Firewall

iptables

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

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

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

19.1.12. Mac OS

brew install mysql
		

启动

brew services start mysql
		





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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySQL server_id
${SERVER_ID}获取放法取得网卡的信息并做md5后的数字作为基数,执行步骤如下:hwadd= ifconfig | grep HWaddr | awk '{print $5}' |md5sum取得当前主机的当前纳秒时间作为盐,执行步骤如下t_salt=date +%s.
1354 0
SQLServer 2012 登陆异常问题
## 背景 RDS FOR SQLServer 2012 已经上线一段时间了,从反馈来看大家遇到了很多权限相关的问题,所以建议把[RDS FOR SQLServer 2012权限提升](https://yq.aliyun.com/articles/71919?spm=5176.100240.searchblog.8.LWPvRT)这篇文章作为基础知识先能够理解;再之后针对大家经常遇到的CASE我
1776 0
SQL Server 2012 新特性:新增和修改函数
转换函数      1.PARSE      Parse是把字符串类型转化为想要的类型,看看和convert和cast的区别         SELECT PARSE ('2.111111' AS float ),CAST ('2.
824 0
通过SQL SERVER添加系统管理员帐号
通过SQL SERVER添加系统管理员帐号,当然是需要有足够的权限,远程链接数据库后执行脚本,脚本如下: /* 此代码是在master数据库下运行 增加系统管理员:mmcgzs 密码:1...
959 0
SQL Server 权限管理
原文:SQL Server 权限管理 标签:SQL SERVER/MSSQL SERVER/数据库/DBA/权限控制/管理/分配/登入名/数据库用户/角色 概述       对数据库系统而言,保证数据的安全性永远都是最重要的问题之一。
1377 0
sql server 跨库访问
最近在做跨库来访问不在同一个数据库的数据,语法格式如下所示: select * from OPENDATASOURCE('SQLOLEDB','Data Source=远程ip;User ID=sa;Password=密码').
780 0
+关注
玄学酱
这个时候,玄酱是不是应该说点什么...
20683
文章
438
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载