“This is the third day of my participation in the August More Text Challenge. For details, see: August More Text Challenge.”

MySQL > MySQL > MySQL > MySQL

1. Daily startup

# /home/service/mysql/support-files/mysql.server start
Starting MySQL SUCCESS!
Copy the code

2. Start maintenance tasks

# mysqld_safe --skip-grant-tables --skip-networking &[1] 12150 [root@VM-0-3-centos ~]# 2021-01-20T04:01:24.495644z MYSqld_safe Logging to '/data/mysql/data/VM-0-3-centos.err'. 2021-01-20T04:01:24.533210z mysqLD_safe Starting mysqld daemon with databases from  /data/mysql/dataCopy the code

In this case, we will temporarily add the arguments we need to the command line, and we will also read the contents below /etc/my.cnf. If the contents conflict, the arguments on the command line will take precedence.

The exit command is as follows:

# mysqladmin -uroot shutdown
Copy the code

3. Use mysqLD_safe to log in

The login# mysqld_safe &exit# mysqladmin -uroot shutdown2021-01-20T04:08:32.753363z mysqLD_safe mysqld from PID file /data/ data/ vm-0-3-centos. Pid ended [1]+ finish mysqld_safeCopy the code

2. Perform initial configuration

2.1 role

The database startup is affected
Client functions, such as login, are affected

2.2 Initial Configuration Methods

Initialize the configuration file (for example, /etc/my.cnf)
Start the Settings on the command line (for example: mysqLD_safe)
Precompile time Settings (compile-install only)

2.3 Writing format of initial Configuration

[tag] XXX = XXX [tag] XXX = XXXCopy the code

2.4 Classification of configuration File Labels

Server:

[mysqld]
[mysqld_safe]
[server]
Copy the code

Client:

[mysql]
[mysqladmin]
[mysqldump]
[client]
Copy the code

2.5 Configuration File Template 5.7

# Server configuration
[mysqld]
# the user
user=mysql
# Software installation directory
basedir=/home/service/mysql
# Data path
datadir=/data/mysql/data
# socket file location
socket=/tmp/mysql.sock
# server ID (1-65535)
server_id=6
# port
port=3306
Copy the code

# Client configuration
[mysql]
# socket file location
socket=/tmp/mysql.sock
Copy the code

2.6 Read Sequence of configuration Files

# mysqld --help --verbose | grep my.cnf
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
Copy the code

MySQL reads these files in turn, so the later file overwrites the previous one, but we do not recommend this!!

2.7 Forcing a User-defined Configuration File to Be Used

--defaults-file
Copy the code

# service mysqld stop
# mysqld_safe --defaults-file=/tmp/aa.cnf &
# mysql
Copy the code

3 MySQL connection management

3.1 the mysql command

TCP/IP way

Mysql -u user name -p Password -h IP -p Port numberCopy the code

Note: To log in using TCP/IP, first see if the user can log in.

Let’s log in and try it:

#Mysql-uwys -p -h 172.21.0.3-p3306
Enter password:
ERROR 1045 (28000): Access denied for user 'wys'@'172.21.0.3' (using password: NO)
Copy the code

Let’s log in again as root to see the current user.

mysql> select user,host from mysql.user;
+---------------+------------+
| user          | host       |
+---------------+------------+| user1 | 10.0.0. % | | user4 | 10.0.0. * | | mysql. The session | localhost | | mysql. The sys | localhost | | root | localhost | |  user2 | localhost | +---------------+------------+
6 rows in set (0.00 sec)
Copy the code

There is no user wys, so let’s create a user and authorize that user.

mysql> create user wys@'172.21.0.3';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on*. *to wys@'172.21.0.3' with grant option;
Query OK, 0 rows affected (0.00 sec)
Copy the code

The login succeeds when the user logs in again.

#Mysql-uwys -p -h 172.21.0.3-p3306
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
......
Copy the code

Note: If we write minus P, it must be preceded by minus h. -h cannot be omitted, but -p can be omitted because the default is 3306.

If we write mysql-uwys-p-p3306, mysql will connect as a local socket.

Local Socket mode

Mysql -u user name -p Password -s socket pathCopy the code

The difference between remote login and local login

Remote login

#Mysql-uwys -p -h 172.21.0.3-p3306
Copy the code

mysql> show processlist;
+----+------+------------------+------+---------+------+----------+------------------+
| Id | User | Host             | db   | Command | Time | State    | Info             |
+----+------+------------------+------+---------+------+----------+------------------+54724 | | | 8 wys | 172.21.0.3: NULL | Query | | 0 starting |show processlist |
+----+------+------------------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)
Copy the code

The local login

# mysql
Copy the code

mysql> show processlist;
+----+------+------------------+------+---------+------+----------+------------------+
| Id | User | Host             | db   | Command | Time | State    | Info             |
+----+------+------------------+------+---------+------+----------+------------------+8 | | wys | 172.21.0.3:54724 | NULL | Sleep | 154 | | NULL | | | 9 root | localhost | NULL | Query | | 0 starting |show processlist |
+----+------+------------------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
Copy the code

As you can see, the two hosts are different. The Host logged in locally is localhost. The Host for remote login is IP: connection thread.

When we type the following command, is the login remote or local?

#Mysql -uwys -s/TMP/mysql-sock -h 172.21.0.3 -p3306
Copy the code

Let’s run the show processlist command again.

mysql> show processlist;
+----+------+------------------+------+---------+------+----------+------------------+
| Id | User | Host             | db   | Command | Time | State    | Info             |
+----+------+------------------+------+---------+------+----------+------------------+8 | | wys | 172.21.0.3:54724 | NULL | Sleep | 1839 | | NULL | | | 9 root | localhost | NULL | Sleep | 1685 | | NULL | | 14 | wys | 172.21.0.3:56436 | NULL | Query | | 0 starting |show processlist |
+----+------+------------------+------+---------+------+----------+------------------+
3 rows in set (0.00 sec)
Copy the code

You can tell it’s remotely logged in.

Conclusion: When both remote and local logins are available, remote logins are preferred.

3.2 Client Tools

sqlyog
navicat

4 Multiple instance Management

4.1 Preparing Multiple Directories

#The mkdir -p/data / 330 7,8,9 {} / data
# cd /data/
# llTotal usage 16 drwxr-xr-x 3 root root 4096 January 20 19:52 3307 drwxr-xr-x 3 root root 4096 January 20 19:52 3308 drWxr-xr-x 3 root root 4096 January 20 19:52 3309 drwxr-xr-x 3 root root 4096 January 13 22:19 mysqlCopy the code

4.2 Preparing the Configuration File

cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/home/service/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF
Copy the code

cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/home/service/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF
Copy the code

cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/home/service/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF
Copy the code

4.3 Initializing three Sets of Data

# mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/home/service/mysql
# mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/home/service/mysql
# mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/home/service/mysql
Copy the code

4.4 Systemd Manages multiple instances

# cd /etc/systemd/system/
# cp mysqld.service mysqld3307.service
# cp mysqld.service mysqld3308.service
# cp mysqld.service mysqld3309.service
Copy the code

Modify the configuration file:

# vim mysqld3307.serviceIs amended as: ExecStart = / home/service/mysql/bin/mysqld -- defaults - file = / data / 3307 / my CNF
# vim mysqld3308.serviceIs amended as: ExecStart = / home/service/mysql/bin/mysqld -- defaults - file = / data / 3308 / my CNF
# vim mysqld3309.serviceIs amended as: ExecStart = / home/service/mysql/bin/mysqld -- defaults - file = / data / 3309 / my CNFCopy the code

4.5 license

# chown -R mysql.mysql /data/*
Copy the code

4.6 start

# systemctl start mysqld3307.service
# systemctl start mysqld3308.service
# systemctl start mysqld3309.service
# ps -ef | grep mysql
mysql    10736     1  2 11:07 ?        00:00:00 /home/service/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
mysql    10774     1  3 11:07 ?        00:00:00 /home/service/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
mysql    10820     1  5 11:07 ?        00:00:00 /home/service/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
root     10859 28919  0 11:07 pts/0    00:00:00 grep --color=auto mysql
Copy the code

4.7 the connection

# mysql -S /data/3307/mysql.sock
# mysql -S /data/3308/mysql.sock
# mysql -S /data/3309/mysql.sock
Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

MySQL learning (3) – based management | MySQL August more challenges

MySQL > MySQL > MySQL > MySQL

2. Perform initial configuration

2.1 role

2.2 Initial Configuration Methods

2.3 Writing format of initial Configuration

2.4 Classification of configuration File Labels

2.5 Configuration File Template 5.7

2.6 Read Sequence of configuration Files

2.7 Forcing a User-defined Configuration File to Be Used

3 MySQL connection management

3.1 the mysql command

TCP/IP way

Local Socket mode

The difference between remote login and local login

3.2 Client Tools

4 Multiple instance Management

4.1 Preparing Multiple Directories

4.2 Preparing the Configuration File

4.3 Initializing three Sets of Data

4.4 Systemd Manages multiple instances

4.5 license

4.6 start

4.7 the connection

MySQL learning (3) – based management | MySQL August more challenges

MySQL > MySQL > MySQL > MySQL

2. Perform initial configuration

2.1 role

2.2 Initial Configuration Methods

2.3 Writing format of initial Configuration

2.4 Classification of configuration File Labels

2.5 Configuration File Template 5.7

2.6 Read Sequence of configuration Files

2.7 Forcing a User-defined Configuration File to Be Used

3 MySQL connection management

3.1 the mysql command

TCP/IP way

Local Socket mode

The difference between remote login and local login

3.2 Client Tools

4 Multiple instance Management

4.1 Preparing Multiple Directories

4.2 Preparing the Configuration File

4.3 Initializing three Sets of Data

4.4 Systemd Manages multiple instances

4.5 license

4.6 start

4.7 the connection

Related Posts

Explain detailed and index optimization best practices

SQL Server 2017 officially released, Microsoft database how to build on the past?

Python multithreading versus multiprocessing