Installation of MySQL as well as basic administrative commands and Settings
- 2020-11-25 07:36:55
- OfStack
MySQL installation
Install Mysql on Linux/UNIX
The RPM package is recommended on the Linux platform to install Mysql. MySQL AB provides download addresses for the following RPM packages:
The following example of Mysql RMP was installed on an SuSE Linux system, although the installation procedure is suitable for other Linux systems that support RPM, such as Centos.
Installation steps are as follows:
Use root to log in to your Linux system.
Download the Mysql RPM package from MySQL.
Perform the Mysql installation by following the command, rpm package for the rpm package you downloaded:
[root@host]# rpm -i MySQL-5.0.9-0.i386.rpm
The above installation of the mysql server creates an mysql user and creates an mysql configuration file my.cnf.
You can find all the binary files related to MySQL in /usr/bin and /usr/sbin. All data tables and databases will be created in the /var/lib/mysql directory.
Here are 1 mysql optional packages that you can install according to your needs:
[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm
Mysql installed on Window
Installing Mysql on Window is relatively easy. All you need to do is download the mysql version from the MySQL download and unzip the package.
Double-click on the setup.exe file. Next, you just need to install the default configuration by clicking "next". By default, the installation information will be in the C:\mysql directory.
You can then switch to C:\mysql\bin at the command prompt by entering "Start" = "in the search box and typing" cmd" = ", and type 1:
mysqld.exe --console
The above command will output 1 mysql startup and InnoDB information if the installation is successful.
Verify the Mysql installation
After the successful installation of Mysql, some basic tables are initialized, and after the server is started, you can verify that Mysql is working properly with a simple test.
Use the mysqladmin tool to get the server status:
Use both mysqladmin commands to check the version of the server. On linux the base 2 file is located at /usr/bin on linux and on window C:\mysql\bin.
[root@host]# mysqladmin --version
This command on linux will output the following results, based on your system information:
mysqladmin Ver 8.23 Distrib 5.0.9-0, for redhat-linux-gnu on i386
If you do not enter any information after the above command, your Mysql has not been installed successfully.
Use MySQL Client(Mysql client) to execute a simple SQL command
You can use the mysql command to connect to the Mysql server on MySQL Client(Mysql client). By default, the Mysql server password is empty, so this example does not require a password.
The order is as follows:
[root@host]# mysql
The above command prints mysql
>
The prompt indicates that you have successfully connected to the Mysql server, which you can do at mysql
>
The prompt executes the SQL command:
mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
+----------+
2 rows in set (0.13 sec)
Mysql after installation
After the Mysql installation is successful, the default root user password is empty. You can use the following command to create the root user password:
[root@host]# mysqladmin -u root password "new_password";
You can now connect to the Mysql server using the following command:
[root@host]# mysql -u root -p
[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm
Note: when you enter the password, the password will not be displayed. You just enter it correctly.
MySQL started when the Linux system started
If you want to start the MySQL server when the Linux system starts, you need to add the following command to the /etc/ rc.local file:
/etc/init.d/mysqld start
Also, you need to add mysqld 2 hexadecimal file to the/etc/init d/directory.
MySQL management
Start and close the MySQL server
First, we need to check that the MySQL server is started by using the following command:
ps -ef | grep mysqld
If MySql is started, the above command will output the list of mysql processes. If mysql is not started, you can use the following command to start the mysql server:
[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm
If you want to shut down the current MySQL server, you can execute the following command:
[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm
MySQL user Settings
If you want to add MySQL users, you just need to add new users to the user table in the mysql database.
The following is an instance of adding a user named guest, with a password of guest123, and authorizes the user to operate SELECT, INSERT and UPDATE:
[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm
Enter password:*******
mysql> use mysql;
[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm
mysql> INSERT INTO user
(host, user, password,
select_priv, insert_priv, update_priv)
VALUES ('localhost', 'guest',
PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host | user | password |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)
As you add users, note that the password is encrypted using the PASSWORD() function provided by MySQL. You can see the user password encryption in the above example is: after 6 f8c114b58f2ce9e.
Note: The FLUSH PRIVILEGES statement needs to be executed in note. This command is executed and the authorization table is reloaded. If you do not use this command, you will not be able to use the newly created user to connect to the mysql server unless you restart the mysql server.
When you create a user, you can specify permissions for the user. In the corresponding permission column, set it to 'Y' in the insert statement. The list of user permissions is as follows:
Another way to add users is through SQL's GRANT command. You will add user zara to the specified database TUTORIALS with the password zara123.
root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
-> ON TUTORIALS.*
-> TO 'zara'@'localhost'
-> IDENTIFIED BY 'zara123';
The above command creates a record of user information in the user table in the mysql database.
Note: SQL's SQL statement is semicolon (;) As an end marker.
/etc/ ES291en.ES292en file configuration
1 In general, you do not need to modify the configuration file, the default configuration of the file is as follows:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
In the configuration file, you can specify the directory in which different error log files are stored. Generally, you do not need to change these configurations.
Commands to administer MySQL
The following is a list of commands that are commonly used when using the Mysql database: