Linux \ CentOS MySQL installation and configuration

Time:2020-3-24

1、 About MySQL

<img width=”250″ hegiht=”150″/>

  • MySQL is a relational database management system, which is developed by MySQL AB company, and now belongs to the product of Oracle.
  • MySQL uses a standardized language. Small size, fast speed, low cost, open source and other characteristics make some small and medium-sized websites choose MySQL as website database.

2、 MySQL installation

1. Installation environment

  • System: CentOS 6.8 64 bit
  • MySQL: mysql-server-5.1.73 Community Edition
  • MySQL CSDN download link

2. Installation steps

1) Execute installation instructions

yum -y install mysql-server
  • Note: whether to execute with sudo permission or not depends on your specific environment

2) Execute the command to check whether MySQL server is installed

rpm -qa|grep mysql-server

Linux \ CentOS MySQL installation and configuration

3、 MySQL configuration

  • Note: the default configuration file is / etc / my.cnf
  • Note: whether to execute with sudo permission or not depends on your specific environment

1. Character set configuration

  • For the problem of Chinese scrambling, in version 5.1, in order to solve the problem of Chinese scrambling, both [MySQL] and [mysqld] in my.ini were added
default-character-set = utf8
  • In version 5.5, you can write this in [MySQL], not in [mysqld], but add
character-set-server=utf8

1) To modify my.cnf configuration:

sudo vim /etc/my.conf

2) Add configuration, under the [mysqld] node:

default-character-set = utf8
character-set-server = utf8

Linux \ CentOS MySQL installation and configuration

3) Save exit

  • Save the exit with the “: WQ” command of vim. If you are still editing, you need to press ESC and enter the command.

2. Self start configuration

1) Execute command first

chkconfig mysqld on

2) Execute again to check the MySQL status. If the 2-5 bit startup status is on.

chkconfig --list mysqld 

Linux \ CentOS MySQL installation and configuration

3. Firewall configuration

1) Edit firewall configuration

sudo vim /etc/sysconfig/iptables

2) Add rule (open 3306 port)

-A INPUT -p tcp -m tcp --dport 3306 -j ACCEPT

3) Save exit

  • Save the exit with the “: WQ” command of vim. If you are still editing, you need to press ESC and enter the command.

4) Restart firewall

sudo service iptables restart

Linux \ CentOS MySQL installation and configuration

4. Start MySQL

sudo service mysqld start

perhaps

/etc/rc.d/init.d/mysqld start

Linux \ CentOS MySQL installation and configuration

5. MySQL environment configuration

Because MySQL has not set the password, you need to set the password to log in to the database server.

  • Set password
mysql -u root
  • Log in as follows:

Linux \ CentOS MySQL installation and configuration

select user,host from mysql.user;

Linux \ CentOS MySQL installation and configuration

  • To delete an anonymous user:
delete from mysql.user where user='';

Linux \ CentOS MySQL installation and configuration

  • View all users
select user,host from mysql.user;

Linux \ CentOS MySQL installation and configuration

  • Insert a user
insert into mysql.user(Host, User, Password) values("localhost", "huaiangg", Password("123456"));
  • View database added users

Linux \ CentOS MySQL installation and configuration

  • Create a database
create database `mmall` default character set utf8 collate utf8_general_ci;
  • View database permissions:
select * from mysql.user \G;
  • Give all permissions (open all IP can connect to ‘%’ because remote connection is required)
--On is followed by the database name. Table name. * indicates all tables under the database
--Root @ localhost means user name @ IP address
--Identified by '123456' indicates the password of the account
--With grant option indicates that you can assign your own permissions to other users
grant all privileges on mmall.* to  [email protected]'%' identified by '123456' with grant option;
  • Change user password
--Root @ localhost - > > user name @ IP
--Password() - > > built in function
set password for [email protected]=Password('123456');
  • Use the user password to log in, type the following instructions, and then enter the password to log in.
mysql -u root -p

4、 MySQL authentication

1. View the IP address of the server running MySQL

ifconfig

Linux \ CentOS MySQL installation and configuration

2. Connect through the client tool (Navicat is used here)

  • Navicat + Crack tool — CSDN download link
  • Reach for the partyContact the author directlyGet the download link.

3. The installation process will not be described. If you are interested, you can read the following articles. Here, you can directly paste the test connection diagram:

  • MySQL 8.0 installation & Navicat for MySQL tutorial

1) Connect to CentOS database

Linux \ CentOS MySQL installation and configuration

2) If the connection is successful, the installation is completed

Linux \ CentOS MySQL installation and configuration

5、 MySQL common commands

1. View the current MySQL users

select user,host,password from mysql.user;

2. Change the root password (using the built-in function)

set password for [email protected]=password('your new password');

perhaps

set password for [email protected]=password('your new password');

3. Exit MySQL

exit

4. Log in again (password required)

mysql -u root -p

5. Delete anonymous users

  • See if there are anonymous users
select user,host from mysql.user;
  • Delete anonymous user (user = ”, ” for empty string)
delect from mysql.user where user = '';
  • Refresh, make operation effective
flush privileges;

6. Add a new MySQL user

insert into mysql.user(Host,User,Password) values("localhost", "yourusername", password("yourpaddword"));
  • Refresh, make operation effective
flush privileges;

7. Create a new database

CREATE DATABASE `db_test` DEFAULT CHARRACTER SET utf8 COLLATE utf8_general_ci;

8. Give all permissions to local users

grant all privileges on db_test.* to [email protected] identified by 'yourpassword';

9. Open all permissions for the account

grant all privileges on db_test.* to 'yourusername'@'%' identified by 'yourpassword';
  • Here, you need to give permissions according to the permissions you need. For example, you need to give all the table add / change query permissions (not delete permissions) under the DB test database to the 192.168.199.111 host computer. The writing method is as follows:
grant select,insert,update  on db_test.* to [email protected]'192.168.199.111' identified by 'yourpassword';

If you are nameless, concentrate on practicing sword!
Like friends can leave your praise!