| 知乎專欄 ｜ 多維度架構 |
目錄
http://downloads.mysql.com/archives.php
安裝環境 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>
準備下面的軟件包
# 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
$ sudo apt-get install mysql-admin
運行mysql-admin
/usr/bin/mysql-admin
運行 mysql-query-browser
mysql-query-browser --query="SELECT * FROM users"
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;
$ 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
#!/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