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

This article mainly introduces how to install and configure mysql5.7 in the centos7 system, the contents of the article are carefully selected and edited by the author, with a certain pertinence, and is of great significance to everyone's reference, the following is to understand with the author how to install and configure mysql5.7 in the centos7 system.

1. Differences between different versions of Mysql:

1. MySQL Community Server community version, open source and free, but does not provide official technical support.

2. MySQL Enterprise Edition Enterprise version, you need to pay for it. You can try it for 30 days.

3. MySQL Cluster cluster version, open source and free. Several MySQL Server can be encapsulated into a Server.

4. MySQL Cluster CGE Advanced Cluster Edition, you need to pay for it.

5. MySQL Workbench (GUI TOOL) is an ER/ database modeling tool specially designed for MySQL. It is the successor to the famous database design tool DBDesigner4.

MySQL Workbench is divided into two versions, namely the community version (MySQL Workbench OSS) and the commercial version (MySQL Workbench SE).

MySQL Community Server is open source and free, and this is the version of MySQL that we usually use. Subdivided into multiple versions according to different operating system platforms

There are three ways to install MYSQL under Linux:

1 download and install online through the yum command

2 download offline rpm installation package installation

3 download source code compilation and installation

MySQL Yum Repository

MySQL officially provides a new way to install MySQL-- using YUM source to install MySQL.

1. Download the YUM source of MySQL on the official website of MySQL. There is a "NEW! MySQL YUM Repository" on the download page of MySQL. Click in to find the corresponding system rpm package for download.

If your Linux system is connected to the Internet, copy the download link: # wget http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm (here is the MySQL YUM source download address of el7, version 5.7)

2. After downloading, you will have a mysql57-community-release-el7-7.noarch.rpm file. You can use the following command to check which packages are included in the file:

# rpm-qpl mysql57-community-release-el7-7.noarch.rpm

/ etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

/ etc/yum.repos.d/mysql-community-source.repo

/ etc/yum.repos.d/mysql-community.repo

Install the RPM package, # rpm-ivh mysql57-community-release-el7-7.noarch.rpm

After installing the above packages, check the yum library, and # yum list Mysql* will generate the following packages in the yum library:

(1can6): mysql-community-common-5.7.11-1.el7.x86_64.rpm | 270 kB

(2can6): mysql-community-devel-5.7.11-1.el7.x86_64.rpm | 3.7MB

(3 MB 6): mysql-community-libs-5.7.11-1.el7.x86_64.rpm | 2.2 MB

(4 MB 6): mysql-community-libs-compat-5.7.11-1.el7.x86_64.rpm | 2.0 MB

(5can6): mysql-community-client-5.7.11-1.el7.x86_64.rpm | 25 MB

(6x6): mysql-community-server-5.7.11-1.el7.x86_64.rpm | 143MB

You can then install MySQL with yum:

# yum install mysql-community-server

The advantage of this is that you can use yum to manage MySQL packages, especially the MySQL installation packages can be generated into the YUM library, more MYSQL installation methods.

Rpm package installation

# wget http://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.11-1.el7.i686.rpm

# yum localinstall mysql-community-server-5.7.11-1.el7.i686.rpm

Installing dependent packages in yum localinstall mode automatically installs dependent packages, which is easier than using # rpm-ivh mysql-community-server-5.7.11-1.el7.i686.rpm method.

Refer to this link if you want to install 5.6:

Http://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/

View installed version

[root@webtest] # mysql-V

Mysql Ver 14.14 Distrib 5.6.31, for Linux (x86 / 64) using EditLine wrapper

Set mysql password

Method 1:

1. Stop the mysql service

# service mysqld stop

2. Change the mysql configuration file to password-free login.

# vi / etc/my.cfg

# Disabling symbolic-links is recommended to prevent assorted security risks

Add this sentence to skip-grant-tables #. You don't need a password to log in to mysql.

Symbolic-links=0

3. Start the mysql service

# service mysqld start

4. Log in to mysql as root and enter the password directly.

# mysql-uroot-p # enter the command to enter, and enter the password prompt to enter directly.

Mysql > set password for root@localhost = password ('123456')

ERROR 1290 (HY000): The MySQL server is running with the-- skip-grant-tables option so it cannot execute this statement

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > set password for root@localhost = password ('123456'); or update user set authentication_string=PASSWORD ("123456") where user= "root"

Query OK, 0 rows affected, 1 warning (0.00 sec)

Mysql > flush privileges; # Update permissions

Mysql > quit; # exit

# service mysqld stop # stop mysql service and restore mysql configuration

# vi / etc/my.cfg

# Disabling symbolic-links is recommended to prevent assorted security risks

# skip-grant-tables # comment out this sentence

Symbolic-links=0

# service mysqld start # start the mysql service

# mysql-uroot-p # enter a new password to log in

Method 2:

To enhance security, a password is randomly generated for root users after installing MySQL5.7. In error log, about the location of error log, if you install the RPM package, the default is / var/log/mysqld.log.

You can obtain the temporary password of MySQL through the # grep "password" / var/log/mysqld.log command

[root@iZ2 ~] # grep "password" / var/log/mysqld.log

2016-04-10T04:53:07.003736Z 1 [Note] A temporary password is generated for root@localhost: dd9FfN/s/&4n

After logging in to the server with this password, you must change the password immediately, otherwise the following error will be reported:

Mysql > select user ()

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

If you just change it to a simple password, the following error will be reported:

Mysql > ALTER USER USER () IDENTIFIED BY '12345678'

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

This is actually related to the value of validate_password_policy. The password you just set must match the length and must contain numbers, lowercase or uppercase letters, and special characters.

Method 3:

# / path/mysqladmin-u UserName-h Host password' new_password'-p

Other ways:

MySQL Manager password setting or modification:

According to the official instructions after version 5.6, the first startup will produce a random password in the root directory, the file name. MySQL _ secret.

[root@bright ~] # cat / root/.mysql_secret

# Password set for user 'root@localhost' at 2015-03-27 23:12:10

: Jj+FTiqvyrF

[root@bright ~] # cd / usr/local/mysql/bin/

[root@bright bin] #. / mysqladmin-u root-h localhost password '123456'-p

Enter password: # enter the second line in .MySQL _ secret for this line

Mysqladmin: [Warning] Using a password on the command line interface can be insecure.

Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

Officially, no matter whether the author uses-- skip-grant-tables to start mysql or not, the test fails. Parents can test:

Shell > mysql-uroot-paired password'# password is the password in .MySQL _ secret

Mysql > SET PASSWORD = PASSWORD ('newpasswd')

There is another way:

Do not generate random passwords during installation, use sudo mysqld-- initialize-insecure

Then set your own password sudo / usr/bin/mysqladmin-uroot password password

Set the password search link: http://www.myhack58.com/Article/sort099/sort0102/2015/60511.htm

Modify the password

Mysql > ALTER USER USER () IDENTIFIED BY '12345678'

Mysql > update mysql.user set authentication_string=password ('newpassword') where User= "root" and Host= "localhost"

Mysql > flush privileges; / / refresh the system permissions table

Mysql > set password=password ("newpassword"); / / change your password

Query OK, 0 rows affected, 1 warning (0.00 sec)

Set permissions

Create a superuser xu that is only allowed to log in locally and allow permissions to be granted to other users with a password of xu@123456

# GRANT ALL PRIVILEGES ON *. * TO xu@'localhost' IDENTIFIED BY 'xw@123456' WITH GRANT OPTION

To create a general program user, this user may only need the permissions of SELECT, INSERT, UPDATE, DELETE, CREATE TEMPORARY TABLES, etc. If you have stored procedures, you need to add EXECUTE permission, which usually specifies the 192.168.100 network segment of the private network.

# GRANT USAGE,SELECT, INSERT, UPDATE, DELETE, SHOW VIEW, CREATE TEMPORARY TABLES,EXECUTE ON `test`.* TO xu@'192.168.100.%' IDENTIFIED BY 'xu@123456'

Create a normal user (only have query permissions)

# GRANT USAGE,SELECT ON `test`.* TO xu@'192.168.100.%' IDENTIFIED BY 'xu@123456'

Refresh and use this command to make permissions effective, especially if you make update or delete updates to those permission tables such as user, db, host, etc. In the past, when the permissions were not updated after using grant, we can get into the habit of using the FLUSH PRIVILEGES command to refresh permissions as long as we make changes to the permissions.

# FLUSH PRIVILEGES

Mysql > grant all on yh_pm.* to 'dba_user'@'%'

Mysql > flush privileges; # Update permissions

View permissions

SHOW GRANTS FOR 'xu'@'192.168.100.%'

Show grants for 'xu'@'%'; # View all permissions of the user

Reclaim permissions, reclaim the DELETE permissions of the previously created webuser user, using the following command

# REVOKE DELETE ON test.* FROM 'xu'@'192.168.100.%'

To delete a user, be careful not to delete the user directly using DELETE, because the user's permissions are not deleted after they are deleted using DELETE, and the previous permissions will be inherited after a new user with the same name is created. The correct thing to do is to use the DROP USER command to delete the user, for example, to delete the 'xu'@'192.168.100.%' user with the following command:

# DROP USER 'xu'@'192.168.100.%'

The GRANT command describes:

ALL PRIVILEGES means all permissions, and you can also use the permissions mentioned by select, update, etc.

ON is used to specify which libraries and tables the permissions are for.

The preceding * sign in *. * is used to specify the database name, and the subsequent * sign is used to specify the table name.

TO means to grant permissions to a user.

Xu@'localhost' stands for xu user, @ followed by restricted host, can be IP, IP segment, domain name and%,% means anywhere. Note: some versions here do not include local. You have previously set% for a user to log in anywhere, but you can't log in locally. This has something to do with the version. If you encounter this problem, just add a localhost user.

IDENTIFIED BY specifies the user's login password.

The WITH GRANT OPTION option means that the user can delegate the permissions he or she has to others. Note: there are often people who create an operating user without specifying the WITH GRANT OPTION option so that the user cannot later use the GRANT command to create a user or authorize another user.

Note: you can use GRANT to add permissions to the user repeatedly, and the permissions are superimposed. For example, if you first add a select permission to the user, and then add an insert permission to the user, then the user has both select and insert permissions.

Rights Management reference:

Http://blog.chinaunix.net/uid-20639775-id-3249105.html

Http://blog.chinaunix.net/uid-10697776-id-2935586.html

What is MySQL Fabric?

MySQL Fabric can "organize" multiple MySQL databases, and the application system distributes tables larger than several TB to multiple databases, namely data fragmentation (Data Shard). There can be multiple databases in the same fragment, and Fabric automatically selects a suitable one as the master database, and the other databases are configured as slave databases to do master-slave replication. When the master database hangs, select one from each slave database to promote the master database. After that, others move from the database to the new master database to copy the new data. Note: by "automatic" here, it is done by MySQL Fabric in the background and does not require the user to change the configuration manually. Most importantly, MySQL Fabric is GPL's open source software, that is, you are free to use and modify the software in accordance with the GPL specification.

Http://www.2cto.com/database/201408/327941.html

III. MySQL Router

MySQL Router is a lightweight MySQL middleware that provides transparent routing between the application and the backend of any MySQL CVM, which can be used to achieve read-write separation.

MySQL Router is best used with MySQL Fabric, but it is not mandatory. Router is best run on the same machine as the application.

MySQL Router is a lightweight middleware used to achieve highly available and scalable functions. Doesn't MySQL Fabric have the same effect? Yes, but one of the biggest disadvantages of MySQL Fabric is that the application needs to be modified, using Fabric's unique Java or python driver, and currently supports only Java,Python and php languages, that is, MySQL Fabric is a highly available and extended function implemented at the driver level. MySQL Router is a middleware, the access protocol is the same as MySQL, and the application does not need to make any changes.

Http://www.codesec.net/view/408108.html

IV. MySQL Utilities

MySQL Utilities is an official MySQL management tool with comprehensive functions. There are mainly five levels of tools: database level (replication, comparison, difference, export, import), audit log level, CVM level (instance cloning, instance information), system level (disk usage, redundant index, search metadata, process), and high availability level (master-slave replication, failover, master-slave synchronization). This tool gives you an edge in the management of mysql.

Https://www.ttlsa.com/mysql/mysql-manager-tools-mysql-utilities-tutorial/

5. What is the difference between mysql-server and mysql-client?

Mysql-server is managed using the mysqld command.

Mysql-client is used to connect to the mysqld service with the mysql command.

In short, one is the service, honest database operation and management, and the other is the tool you use to access the database.

For example, the WEB service of a website and the browser you use to browse the web.

Delete mysql installed in yum mode

Yum remove mysql mysql-server

Rm-rf / var/lib/mysql

Rm / etc/my.cnf

Check to see if mysql software is still available:

Rpm-qa | grep mysql

If it exists, just continue to delete it.

Installation in source mode

Yum-y install gcc gcc-c++ gcc-g77 autoconf automake zlib* fiex* libxml* ncurses-devel libmcrypt* libtool-ltdl-devel* make cmake

Yum install bison

Mkdir-p / usr/local/mysql5.6.10

Mkdir-p / data/mysql

Cd / data

Groupadd mysql

Useradd-r-g mysql mysql

Ln-s / usr/local/mysql5.6.10 / usr/local/mysql

Cd mysql-5.6.10

Cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql5.6.10-DMYSQL_UNIX_ADDR=/tmp/mysql.sock-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci-DWITH_MYISAM_STORAGE_ENGINE=1-DWITH_INNOBASE_STORAGE_ENGINE=1-DWITH_ARCHIVE_STORAGE_ENGINE=1-DWITH_BLACKHOLE_STORAGE_ENGINE=1-DWITH_MEMORY_STORAGE_ENGINE=1-DWITH_PARTITION_STORAGE_ENGINE=1-DWITH_READLINE=1-DENABLED_LOCAL_INFILE=1-DMYSQL_DATADIR=/data/mysql-DMYSQL_USER=mysql-DMYSQL_TCP_PORT=3306

Make install

Chown-R mysql:mysql / usr/local/mysql5.6.10

Chown-R mysql:mysql / data/mysql

Cd / usr/local/mysql5.6.10/support-files

Cp mysql.server / etc/init.d/mysqld

Cd / usr/local/mysql5.6.10/scripts/

. / mysql_install_db-- user=mysql-- basedir=/usr/local/mysql5.6.10-- datadir=/data/mysql

After reading the above about how to install and configure mysql5.7 in centos7 system, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.