MySQL 5.7.22 多實例安裝（二進制免編譯安裝模式） · 個人筆記

1.數據庫 mysql 5.7.17 下載 (623.7MB) https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz >> linux generic >> Linux - Generic (glibc 2.5) (x86, 64-bit), Compressed TAR Archive（Archive代表免編譯安裝） 2.基本配置： ~~~ shell> groupadd mysql shell> useradd -r -g mysql -s /bin/false mysql shell> ll /root/mysql-5.7.15-linux-glibc2.5-x86_64.tar.gz shell> cd /root shell> tar -zxvf mysql-5.7.15-linux-glibc2.5-x86_64.tar.gz shell> mv /root/mysql-5.7.15-linux-glibc2.5-x86_64 /usr/local/mysql shell> cd /usr/local/mysql shell> mkdir -p /var/run/mysqld shell> mkdir -p /home/mysql/data3306 /home/mysql/binlog3306 shell> mkdir -p /home/mysql/data3307 /home/mysql/binlog3307 shell> chmod 750 /var/run/mysqld /home/mysql/data* /home/mysql/binlog* shell> chown -R mysql:mysql /home/mysql/ /var/run/mysqld ~~~ ``` 3.配置參數文件 shell> vi /etc/my.cnf [mysqld_multi] mysqld = /usr/local/mysql/bin/mysqld_safe mysqladmin = /usr/local/mysql/bin/mysqladmin #user = root #password = rootpwd [mysqld3306] port = 3306 server_id = 3306 basedir =/usr/local/mysql datadir =/home/mysql/data3306 log-bin=/home/mysql/binlog3306/mysql-bin socket =/tmp/mysql3306.sock log-error =/var/log/mysqld3306.log pid-file =/var/run/mysqld/mysqld3306.pid [mysqld3307] port = 3307 server_id = 3307 basedir =/usr/local/mysql datadir =/home/mysql/data3307 log-bin=/home/mysql/binlog3307/mysql-bin socket =/tmp/mysql3307.sock log-error =/var/log/mysqld3307.log pid-file =/var/run/mysqld/mysqld3307.pid ~~~ 4.添加環境變量 ~~~ shell> echo "PATH=$PATH:/usr/local/mysql/bin " >> /etc/profile shell> source /etc/profile ~~~ 5.安裝,完成后記住root密碼 ~~~ shell> /bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/home/mysql/data3306 --explicit_defaults_for_timestamp shell> bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/home/mysql/data3307 --explicit_defaults_for_timestamp ~~~ 6.查看密碼 [root@localhost mysql]# bin/mysqld --initialize --user=mysql --datadir=/home/mysql/data3306 --explicit_defaults_for_timstamp 2018-06-14T08:53:58.531057Z 0 [Warning] InnoDB: New log files created, LSN=45790 2018-06-14T08:53:58.602686Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2018-06-14T08:53:58.665198Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this erver has been started. Generating a new UUID: 7a01969a-6fb0-11e8-937f-000c292641c5. 2018-06-14T08:53:58.666785Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2018-06-14T08:53:58.668179Z 1 [Note] A temporary password is generated for root@localhost: =PkN#o>Bk5*2 密碼為‘’=PkN#o>Bk5*2‘’ 7.啟動實例服務 ~~~ shell> mysqld_multi report shell> mysqld_multi start 3306,3307 shell> netstat -ntlp | grep mysql shell> ll /tmp/mysql*.sock ~~~ 8.訪問管理 ~~~ # 訪問數據庫(進入后要求更改root密碼) shell> mysql -u root -p -S /tmp/mysql3306.sock Enter password: shell> mysql -u root -p -S /tmp/mysql3307.sock Enter password: mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'dongshang2018'; mysql> select Host,User from mysql.user; mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'dongshang2018' WITH GRANT OPTION; mysql>FLUSH PRIVILEGES; # 超級管理員 mysql> create user root@'192.168.1.%' IDENTIFIED by 'rootpwd'; mysql> grant all privileges on *.* to root@'192.168.1.%'; mysql> flush privileges; # 可用端口登錄了(此時登錄賬戶為: root@'192.168.1.%') shell> mysql -h 192.168.1.110 -u root -p -P3306 shell> mysql -h 192.168.1.110 -u root -p -P3306 ~~~ 9.關閉某個實例 ~~~ shell> mysqladmin -uroot -pdongshang2018 -S /tmp/mysql3306.sock shutdown shell> mysqladmin -uroot -pdongshang2018 -S /tmp/mysql3307.sock shutdown ~~~ 10.添加自啟動 ~~~ shell> cat /etc/rc.local mkdir /usr/local/mysql/init touch /usr/local/mysql/init/mysqld_pid.sh vim /usr/local/mysql/init/mysqld_pid.sh mkdir -p /var/run/mysqld chmod 750 /var/run/mysqld chown -R mysql:mysql /var/run/mysqld mysqld_multi start 3306,3307 shell> echo "/usr/local/mysql/bin/mysqld_multi --defaults-extra-file=/etc/my.cnf start 3306,3307" >> /etc/rc.local ~~~ 11.修改php.ini (1)mysql_connect()“No such file or directory”錯誤 pdo_mysql.default_socket=/tmp/ pdo_mysqli.default_socket=/tmp/ (2)SQLSTATE[HY000] [2002] Connection refused錯誤解決修改tp5 database.php文件 localhost 改為 127.0.0.1 12.linux重啟后，mysql無法啟動。原因是 /var/run/mysqld 目錄是保存在內存里的，重啟后會消失