Firstly update your desktop and install the SQL server
sudo apt-get update sudo apt-get install mysql-server
Verify that the server is running with the following command
sudo systemctl start mysql.service sudo systemctl status mysql.service
The result should look like this:
You will need to set the password for the root account if you are running the installation on an Ubuntu machine because authentication is deactivated on Ubuntu by default. So as to avoid an error, you’ll need to configure the root account authentication method
sudo mysql
Change the password for root using ALTER USER:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Exit after making this change
mysql> exit
In securing your server, you will need to execute the following command to setup your password policy
sudo mysql_secure_installation
The password policy given will apply to subsequent users accounts created.
The next is to authenticate using the root user’s password:
mysql -u root -p
This command gives the root user access to the MySQL cli, and also to interact directly with the MySQL server.
Then go back to using the default authentication method using this command:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
This will allow you connect using the sudo mysql command again.
It is bad OpSec to use the root account to perform regular day-to-day action on the database. The best option is to create a user account with limited privileges.
This is first done by logging in as root with the following command:
sudo mysql
Alternatively, if you have previously set a password for the root account use this instead:
mysql -u root -p
Next create a new user:
mysql> CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';
After entering the command, follow the prompt and fill in your username, hostname (localhost if you’re using Ubuntu).
For authentication, you have the options of using auth_socket plugin which provides string security without requiring a password but has a shortcoming of preventing remote connections,authentication_plugin plugin, caching_sha2_password which is the default MySQL plugin, but its shortfall is that some versions of PHP are not compatible with it or mysql_native_password plugin.
mysql> CREATE USER 'jack'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
You can also alter an existing user using:
mysql> ALTER 'jack'@'hostname' IDENTIFIED WITH mysql_native_password BY 'password';
After creating the user, you can assign it with privileges with the following syntax.
mysql> GRANT PRIVILEGE ON database.table TO 'username'@'host';
Using GRANT ALL PRIVILEGES instead will give the user superuser privileges similar to that of root. Such flag will otherwise defeat the purpose of creating a separate user account from root.
The PRIVILEGE variable represents what action a user is allowed to perform. Global privileges can also be granted by replacing database.table with *.
Below we will be granting user permissions to create, modify, delete, insert, select, update and delete data from a table by using CREATE, ALTER, DROP, INSERT, SELECT, UPDATE and DELETE respectively.
mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT on *.* TO 'jack'@'hostname' WITH GRANT OPTION;
The WITH GRANT OPTION flag allows the user to grant privileges it has to other users.
Next we will use the FLUSH PRIVILEGES command to empty the cache and free up memory:
mysql> FLUSH PRIVILEGES;
After you can exit the MySQL cli
mysql> exit
You can now log in back using your credentials
mysql -u jack -p
You can now verify that the MySQL server is running with the following command:
systemctl status mysql.service
Alternatively, you can connect to the MySQL database using the administrative command tool mysqladmin.
sudo mysqladmin -p -u jack version
以上是Step-by-step guide for how to install an SQL server on Ubuntu 04的詳細內容。更多資訊請關注PHP中文網其他相關文章！
