Shulou(Shulou.com)06/03 Report--

This article includes the installation and configuration of mysql service, and simple use, as well as the authorization management of mysql user account. The first part is installation, and the second half is user authorization management and simple use (add, delete, modify and query):

Prepare before installation:

1. Centos7 one, prepare the system image, and configure the yum repository by yourself

2. Download and install the required software packages, link: https://pan.baidu.com/s/13YAoybOtX55XKGQIzIxsIA

1. Install and configure MySQL database:

In order to ensure the integrity and customization of the MySQL database function, I installed the MySQL database system by compiling and installing the source code. The MySQL5.X series version is the most widely used, and its stability and compatibility are good. The official site for downloading the source code package is https://www.mysql.com.

Now that MySQL has been acquired by Oracle, and Oracle intends to develop MySQL into a paid product, in order to avoid future copyright problems, Linux changed the MySQL database to MariaDB,MariaDB and MySQL. There is not much difference in function and use. However, most people think that MySQL source installation is better than using MariaDB on the Linux system disk, so use source code installation. At least it's free for now.

1. In order to avoid program conflicts, port conflicts and other problems, you can first execute the following command to delete the mysql program that comes with the system:

[root@localhost ~] # yum-y erase mysql

2. Mount the centos system disk and install the ncurses-devel package:

[root@localhost ~] # mount / dev/cdrom / media [root@localhost ~] # cd / media/Packages/ [root@localhost Packages] # rpm-ivh ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm

3. Since the mysql 5.x series version requires cmake compilation and installation, continue to install the cmake package:

[root@localhost media] # tar zxf cmake-2.8.6.tar.gz-C / tmp # unpack [root@localhost media] # cd / tmp/cmake-2.8.6/ [root@localhost cmake-2.8.6] #. / configure & & gmake & & gmake install # configuration, compile and install

4. Create a user specially used to run the mysql service. This user does not need to log in directly to the system:

[root@localhost cmake-2.8.6] # groupadd mysql [root@localhost cmake-2.8.6] # useradd-M-s / sbin/nologin mysql-g mysql

5. Decompress the downloaded mysql source package, configure, compile and install it (be sure to pay attention to case, do not type wrong configuration items, and continue the subsequent installation even if there is an error. However, in the end, the service cannot be started, do not type the wrong letters, and say the important things three times):

[root@localhost media] # tar zxf mysql-5.6.36.tar.gz-C / tmp # extract to / tmp directory [root@localhost cmake-2.8.6] # cd / tmp/mysql-5.6.36 # switch to the expanded source directory [root@localhost mysql-5.6.36] # cmake- DCMAKE_INSTALL_PREFIX=/usr/local/mysql-DSYSCONFDIR=/etc-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_ General_ci-DWITH_EXTRA_CHARSETS= all [root @ localhost mysql-5.6.36] # make & & make install # compile and install

3. Since the mysql 5.x series version requires cmake compilation and installation, continue to install the cmake package:

6. Set permissions to the database directory:

[root@localhost mysql-5.6.36] # chown-R mysql:mysql / usr/local/mysql

7. Establish a configuration file:

The MariaDB database is supported by default on the centos 7 system, so the default / etc/my.cnf configuration file of the system is the configuration file of MariaDB, while the default sample configuration file of mysql database is provided in the source directory of mysql. Before starting the mysql database, you need to replace the original my.cnf file with the configuration file provided by mysql.

[root@localhost mysql-5.6.36] # rm-rf / etc/my.cnf # Delete the original configuration file [root@localhost mysql-5.6.36] # cp support-files/my-default.cnf / etc/my.cnf # copy the configuration file in the source package to / etc/

8. Initialize the database (if the database is configured incorrectly during initialization, delete the data directory in the mysql installation directory and reinitialize it):

[root@localhost mysql-5.6.36] # / usr/local/mysql/scripts/mysql_install_db-user=mysql-basedir=/usr/local/mysql-datadir=/usr/local/mysql/data

9. Set the environment variable (to facilitate the use of the mysql command in any directory):

[root@localhost mysql-5.6.36] # echo "PATH=$PATH:/usr/local/mysql/bin" > / etc/profile [root@localhost mysql-5.6.36] #. / etc/profile # effective immediately

10. Add system services (in two ways, read this section, and then select one of them):

1) if you want to add mysqld system services for management through systemctl, you can directly use the service script provided in the source package, as follows:

[root@localhost mysql-5.6.36] # cp support-files/mysql.server / usr/local/mysql/bin/mysqld.sh # copy to the installation directory [root@localhost mysql-5.6.36] # chmod + x / usr/local/mysql/bin/mysqld.sh # gives execution permission

2) create the configuration file / usr/lib/systemd/system/mysqld.service of the mysql system service and add it as the mysqld system service:

The above configuration file is typed by hand. After writing and saving, you can use the systemctl tool to control the mysql database service.

Method 2:

If you find it troublesome to write a configuration file, you can do it another way:

When the execute permission has been granted to / usr/local/mysql/bin/mysqld.sh, continue with the following:

[root@localhost mysql-5.6.36] # cp / usr/local/mysql/bin/mysqld.sh / etc/init.d/mysqld

[root@localhost mysql-5.6.36] # vim / etc/init.d/mysqld

After modification, save and exit. Continue with the following command:

[root@localhost mysql-5.6.36] # chkconfig-- add mysqld # added as a system service

These are the two ways to add system services. You can choose one of the two, but method 2 cannot use the systemctl enable mysqld command to set up the boot automatically.

Start the service and log in to the mysql server:

[root@localhost mysql-5.6.36] # systemctl start mysqld # start service [root@localhost mysql-5.6.36] # systemctl status mysqld # check whether the service status is normal [root@localhost mysql-5.6.36] # mysql- u root # log in to the mysql database using the root user

It should be noted that this root account has nothing to do with the system root account, but coincidentally, the administrator account of mysql is also root. There is no password for the initial installation. You can configure the password using the following command:

[root@localhost /] # mysqladmin-u root password # set password for user root New password: # enter password Confirm new password: # reconfirm

If the user has a password and wants to change the password, use the following command:

[root@localhost /] # mysqladmin-u root-p password # change password Enter password: # enter old password New password: # enter new password Confirm new password: # reconfirm

If the user does not have a password, use the following command to log in to the mysql database:

[root@localhost mysql-5.6.36] # mysql- u root

If the user has a password, you need to add the-p option:

[root@localhost /] # mysql-u root-p

After logging in to the mysql server, you can execute the SQL statement, and each mysql operation statement ends with a semicolon ";". If you press enter without entering a semicolon, it is equivalent to a line break. All commands are case-insensitive, use status to view the basic information of the current database service, and use exit to exit the mysql command tool.

Second, database user authorization and simple operation (addition, deletion, modification and query):

1. Grant permissions:

Grant permission list on library name. Table name to username @ source address [identified by 'password']

When using the grant statement, you need to pay attention to the following points:

Example of grant usage:

Mysql > grant select on test.* to 'zhangsan'@'localhost' identified by' 123456'; # create a user name of Zhang San and password of 123456, log in with the local host, and execute select statements on all tables in the test library.

2. View the permissions of zhangsan to log in using the local host:

Mysql > show grants for 'zhangsan'@'localhost'

3. Revoke the permission:

Mysql > revoke all on test.* from 'zhangsan'@'localhost'; should pay attention to how libraries and tables are specified when granting permissions, and must be specified in the same way when revoking permissions. If test.user (user table in test library) is used when granting permissions, an error will be reported if test.* is used when revoking permissions.

4. View all the libraries in the current server:

Mysql > show databases

5. Use the use statement to switch the library and view the tables in the library:

Mysql > use mysql; # switch to mysql library mysql > show tables; # to view all tables in the library

6. View the structure of the table:

Mysql > use mysql; # switch to mysql library mysql > describe user; # to view the structure of the table

7. Create a new library:

Mysql > create database test2; # create a library called test2.

8. Create a new table:

Mysql > create table table name (column name 1 type, column name 2 type,.... , primary key (primary key name)

For example, create a simple employee information table:

Mysql > create table yuangongxinxi (xingming char (16) not null, xingbie char (4)), nianling int, gonghao int, primary key (gonghao); # create a new table named employee Information. Contains columns such as name, age, job number, etc., and sets the job number as the primary key.

9. Insert an employee information into the table just now:

Mysql > insert into yuangonxinxi (xingming,xingbie,nianling,gonghao) values ('zhangsan','nan','25','100')

10. Query the data in the table (you can also add a where statement to specify a specific row for the query, or use a column name instead of an asterisk to query a specific column):

Mysql > select * from yuangonxinxi

11. Modify the data record:

Mysql > update yuangonxinxi set nianling='30' where xingming='zhangsan'; # modify the age of zhagnsan to 30

12. Delete data records:

Mysql > delete from yuangonxinxi where xingming='zhangsan'; # Delete zhangsan's data record

13. Delete the data table you just created:

Mysql > drop table test2.yuangongxinxi

14. Delete the library you just created:

Mysql > drop database test2

-Thank you for reading

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.