MySQL：常見使用問題，mysql使用

MySQL：常見使用問題，mysql使用

1、Linux 上安裝MySQL

安裝步驟：

1）解壓 tar.gz文件

shell> tar -zxvf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz

2）初始化默認數據庫（mysql、performace_schema、sys、information_schema）

在/home/bes/jinuo/mysql 目錄下的結構如下：

/home/bes/jinuo/mysql
                     /mysql-5.7.9-glibc2.5-x86_64
                           /bin
                           /docs
                           /include
                           /lib
                           /man
                           /share
                           /support-files
                    /test
                         /ins1
                              /my-default.cnf

拷貝 support-files 目錄到你想要做mysql實例的目錄下，並編輯如下：

[mysqld]
basedir=/home/bes/jinuo/mysql/mysql-5.7.9-linux-glibc2.5-x86_64
datadir=/home/bes/jinuo/mysql/test/ins1/datadir
port=36001
server_id=36001
socket=/home/bes/jinuo/mysql/test/ins1/mysql.sock
log-error=/home/bes/jinuo/mysql/test/mysqld.log
explicit_defaults_for_timestamp=true
character-set-server=utf8
collation-server=utf8_general_ci
skip-host-cache
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

然後執行如下命令初始化：

shell> bin/mysql_install_db    # Before MySQL 5.7.6
shell> bin/mysqld --initialize   # MySQL 5.7.6 and up

在初始化時，會為root用戶 創建一個臨時密碼。臨時密碼的位置可以這樣找到：

MySQL 5.6.x ：

A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.
You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.
Also, the account for the anonymous user has been removed.

MySQL 5.7.x :

如果初始化時使用的是  --initialize：
# tail -n1 /home/bes/jinuo/mysql/test/ins1/mysqld.log
2016-12-11T07:47:58.199154Z 1 [Note] A temporary password is generated for root@localhost: wzgds/:Kf2,g

如果

初始化時使用的是  --initialize-insecure：

  # tail -n1 /var/log/mysql/error.log
  2016-12-11T07:51:28.506142Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option

3）啟動數據庫

啟動MySQL Server：

shelll> /home/bes/jinuo/mysql/mysql-5.7.9-linux-glibc2.5-x86_64/bin/mysqld --defaults-file=/home/bes/jinuo/mysql/test/ins1/my-default.cnf &

4）修改root密碼、授權

       a: 停止 MySQL Server

       b: 繞過授權檢查方式啟動MySQL Server

shell> /home/bes/jinuo/mysql/mysql-5.7.9-linux-glibc2.5-x86_64/bin/mysqld --defaults-file=/home/bes/jinuo/mysql/test/ins1/my-default.cnf --skip-grant-tables &

       c: root用戶登錄到mysql server上，並切換到mysql 庫

shell> /home/bes/jinuo/mysql/mysql-5.7.9-linux-glibc2.5-x86_64/bin/mysql --socket=/home/bes/jinuo/mysql/test/ins1/mysql.sock -uroot -p

mysql> use mysql;

       d: 修改root 用戶的密碼： 

mysql> update mysql.user set authentication_string = password('mypassword') where user = 'root';
mysql> flush privileges;
mysql> quit;

        e: 停止mysql server，正常啟動。

        正常啟動的方式在前面 3）中已說過。

        f: root 登錄後，進行授權調整：

shell> /home/bes/jinuo/mysql/mysql-5.7.9-linux-glibc2.5-x86_64/bin/mysql --socket=/home/bes/jinuo/mysql/test/ins1/mysql.sock -uroot -p
Enter Password

mysql> grant all on *.* to 'root'@'%' identified by 'yourRootPassword';