Contents
- Japanese Edition
- 1. Environment
- 2. Install MySQL
- 3. Reset Root Password
- 4. Connect to MySQL
- 5. Notes
⚓ 1. Environment
⚓ 2. Install MySQL
⚓ 2-1. Install MySQL Client
$ sudo apt install mysql-client
⚓ 2-2. Install MySQL Server
$ sudo apt install mysql-server
⚓ 2-3. Check Version
Check if MySQL is installed.
$ mysql --version mysql Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using EditLine wrapper
⚓ 2-4. Check Behaviour
⚓ 2-4-1. Start
$ sudo /etc/init.d/mysql start
⚓ 2-4-2. Stop
$ sudo /etc/init.d/mysql stop
⚓ 2-4-3. Restart
$ sudo /etc/init.d/mysql restart
⚓ 3. Reset Root Password
⚓ 3-1. Prepare for Connecting to MySQL without Authentication
$ sudo mysqld_safe --skip-grant-tables & [1] 5676
* --skip-grant-tables & allows anyone to log in MySQL anywhere and anytime.
It is highly dangerous, so it should not be used except the initial settings.
Please refer to Official Document for the details.
⚓ 3-2. Connecting to MySQL without Password as Root User
Log in as the root user with -u.
$ sudo mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu) Copyright (c) 2000, 2019, 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>
⚓ 3-3. Password Setting
mysql> UPDATE mysql.user SET authentication_string = "", password_expired = "N" WHERE User = "root"; mysql> UPDATE mysql.user SET plugin="mysql_native_password" WHERE User="root"; mysql> FLUSH PRIVILEGES; exit
Please see the explamation of each SQL shown below.
⚓ 3-3-1. UPDATE mysql.user SET authentication_string = "", password_expired = "N" WHERE User = "root";
DB_NAME.TABLE_NAME finds the DB and table to update.
Set the password in authentication_string column and N in password_expired where the user is root.
⚓ 3-3-2. UPDATE mysql.user SET plugin = "mysql_native_password" WHERE User = "root;
Set mysql_native_password in authentication_string column fpr authentication plugin in the account where the user is root.
⚓ 3-3-3. FLUSH PRIVILEGES;
Reload on Grant Tables to reflect all changes.
⚓ 4. Connect to MySQL
⚓ 4-1. Prepare for Connecting to MySQL with Authentication
$ sudo mysqld_safe
⚓ 4-2. Connect to MySQL with Password
Log in as the root user with the password using -u and p.
$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu) Copyright (c) 2000, 2019, 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>
Now MySQL setup is done.
⚓ 5. Notes
Refer to user table in mysql database with the SQL below.
⚓ 5-1. Show DB
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.01 sec)
⚓ 5-2. Go into DB
mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
⚓ 5-3. Show Tables
mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 31 rows in set (0.01 sec)
⚓ 5-4. Get Record from Table
mysql> select * from user; +-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ | Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | +-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ | localhost | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | auth_socket | | N | 2019-11-08 00:00:00 | NULL | N | | localhost | mysql.session | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *HOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGE | N | 2019-11-08 00:00:00 | NULL | Y | | localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *HOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGE | N | 2019-11-08 00:00:00 | NULL | Y | | localhost | debian-sys-maint | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | mysql_native_password | *HOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGEHOGE | N | 2019-11-08 00:00:00 | NULL | N | +-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+ 4 rows in set (0.00 sec)
* w3schools.com explains SQL usages in detail.