第 1 章 MySQL Server
太叔昊穹
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 系列 手札
本文作者：陈景峯
转载请与作者联系，同时请务必标明文章原始出处和作者信息及本声明。
类似资料：