MySQL database installation, configuration and basic operations

MySQL database installation, configuration and basic operations
1. MySQL database: when it comes to the development of some large Web systems or embedded software, it is indispensable to use databases to manage data. In Windows, we have used a variety of databases, such as sqlServer, Oracle, and MySQL. We know that the software installation and configuration on Windows systems are interface-based, the operation is obvious and simple. In the Linux operating system, it seems that MySQL is the first choice of databases. It is a relational database management system developed by MySQL AB in Sweden and currently belongs to Oracle. MySQL is an associated database management system that stores data in different tables rather than in a large warehouse. This increases the speed and flexibility. The SQL language of MySQL is the most commonly used standard language for accessing databases. MySQL adopts the dual Authorization Policy (this term "Authorization Policy"). It is divided into community edition and commercial edition. because of its small size, fast speed, and low total cost of ownership, especially open source code, MySQL is generally used as the website database for the development of small and medium-sized websites. Due to its superior performance, the Community edition works with PHP and Apache to form a good development environment. Today, I have read the MySQL section in Linux program design and recorded it based on the steps for installing and configuring it in a virtual machine for future study and review. 2. mySQL installation steps: 2.1 check whether the mysql database already exists in your system. Because mysql database is too popular on Linux, the mainstream Linux system versions downloaded are basically integrated with the MySQL database, run the following command to check whether the mysql database has been installed on our operating system:

[root@localhost ~]# rpm -qa | grep mysql

We use the root account to log on to the system. Because MySQL has been installed on my system, we can obtain the installed version information in the system:
If it is not installed, a prompt is displayed. you can skip the uninstallation step 2. 2.2 uninstall the original MySQL and run the following command to uninstall the existing MySQL:

[root@localhost ~]# rpm -e --nodeps mysql

At this point, the installation record of MySQL does not exist in the system. 2.3 install MySQL in yum mode to install mysql database in yum mode. First, we can enter the yum list | grep mysql command to view the mysql database downloadable version on yum:

[root@localhost ~]#yum list | grep mysql

We can see that there are many MySQL downloadable versions on the yum server:

Then, run the yum install-y mysql-server mysql-devel command to install mysql-server mysql-devel.

[root@localhost ~]# yum install -y mysql-server mysql mysql-devel

After several seconds, we can see the following information:

When we see the pleasing word complete, it means we have successfully installed the MySQL database. 3. mySQL Database Configuration 3.1 MySQL service settings after installing the mysql database, you will find an additional mysqld service. This is our database service, we can start our mysql service by entering the service mysqld start command.

Generally, we set the service of the database to auto-start upon startup, use the command chkconfig mysqld on to set it, and run the command chkconfig -- list | grep mysql to view the result:

[root@localhost ~]# chkconfig mysqld on[root@localhost ~]# chkconfig --list | grep mysqlmysqld         0:off1:off2:on3:on4:on5:on6:of

3.2 The account password is set after we have installed the MySQL database, there is a default Administrator account named root, at this time, you need to manually set the password: after you set the password by running the mysqladmin-u root password 'root' command, run mysql-u root-p to log on to the database:
Now, the MySQL database configuration is complete. 4. MySQL basic operation 4.1 basic configuration file (1) stores the main configuration information of the MySQL database in/etc/my. cnf. View:

[root@localhost ~]# cat /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[root@localhost ~]# 

(2) store the database files in/var/lib/mysql. For details, see:

[root@localhost mysql]# cd /var/lib/mysql[root@localhost mysql]# ls -ltotal 20500-rw-r--r--. 1 root  root     10717 May 13 10:34 create-rw-rw----. 1 mysql mysql 10485760 May 13 10:27 ibdata1-rw-rw----. 1 mysql mysql  5242880 May 13 10:27 ib_logfile0-rw-rw----. 1 mysql mysql  5242880 May 13 10:27 ib_logfile1drwx------. 2 mysql mysql     4096 May 13 10:27 mysqlsrwxrwxrwx. 1 mysql mysql        0 May 13 10:27 mysql.sockdrwx------. 2 mysql mysql     4096 May 13 10:27 test[root@localhost mysql]# 

4.2 basic database operations (1) Create a database from the above default file. We can see that there are two default databases in our MySQL database: mysql and test. Run the following command to create a database:

[root@localhost mysql]# mysql -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 21Server version: 5.1.73 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> create database yr;Query OK, 1 row affected (0.00 sec)mysql> exitBye[root@localhost mysql]# 

First, we use the root account to log on to the database. We can see the mysql> command prompt symbol and run the create database name command to create the database name. The message is displayed, indicating that the database is successfully created. exit with the command. Now, we can see the created database yr under/var/lib. mysql:

[root@localhost mysql]# ls -ltotal 20504-rw-r--r--. 1 root  root     10717 May 13 10:34 create-rw-rw----. 1 mysql mysql 10485760 May 13 10:27 ibdata1-rw-rw----. 1 mysql mysql  5242880 May 13 10:27 ib_logfile0-rw-rw----. 1 mysql mysql  5242880 May 13 10:27 ib_logfile1drwx------. 2 mysql mysql     4096 May 13 10:27 mysqlsrwxrwxrwx. 1 mysql mysql        0 May 13 10:27 mysql.sockdrwx------. 2 mysql mysql     4096 May 13 10:27 testdrwx------. 2 mysql mysql     4096 May 13 14:41 yr[root@localhost mysql]# 

(2) create a table and add the following data. log on to MySQL again, create a table in the database we created above, and add several pieces of data:

[root@localhost mysql]# mysql -u root -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 23Server version: 5.1.73 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> use yr;Database changedmysql> create table user(    -> Id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,    -> Name VARCHAR(50),    -> age INTEGER);Query OK, 0 rows affected (0.07 sec)mysql> 

Now, a user table is successfully created in the yr database, which has three attributes: Id, Name, and age. Id is the primary key. Add and query data:

mysql> insert into user(Id , Name , age)values(1,"aa",20);Query OK, 1 row affected (0.10 sec)mysql> insert into user(Id , Name , age)values(2,"bb",21);Query OK, 1 row affected (0.00 sec)mysql> select Id , Name , age from user;+----+------+------+| Id | Name | age  |+----+------+------+|  1 | aa   |   20 ||  2 | bb   |   21 |+----+------+------+

(3) delete a data entry with Id = 1 from the database table:

mysql> delete from user where Id=1;Query OK, 1 row affected (0.05 sec)mysql> select Id , Name , age from user;+----+------+------+| Id | Name | age  |+----+------+------+|  2 | bb   |   21 |+----+------+------+1 row in set (0.00 sec)mysql> 

(4) use the drop command to delete database tables and databases:

mysql> drop table user;Query OK, 0 rows affected (0.02 sec)

mysql> drop database yr;Query OK, 0 rows affected (0.28 sec)mysql> exitBye[root@localhost mysql]# lltotal 20500-rw-r--r--. 1 root  root     10717 May 13 10:34 create-rw-rw----. 1 mysql mysql 10485760 May 13 10:27 ibdata1-rw-rw----. 1 mysql mysql  5242880 May 13 10:27 ib_logfile0-rw-rw----. 1 mysql mysql  5242880 May 13 10:27 ib_logfile1drwx------. 2 mysql mysql     4096 May 13 10:27 mysqlsrwxrwxrwx. 1 mysql mysql        0 May 13 10:27 mysql.sockdrwx------. 2 mysql mysql     4096 May 13 10:27 test[root@localhost mysql]# 

The above is all about mysql installation, configuration, and usage in linux.