1, MySQL 5 6 and MySQL 5 7 difference between

1. bostorg was added at cmake
2. Use mysqld --initialize instead of MySQL during initialization_ install_ DB, other parameters remain unchanged: - user= --basedir= --datadir=
3. --initialize generates a temporary password
4. You can also use another parameter -- initialize execute

[root@dba-01 ~]# yum install -y gcc gcc-c++ automake autoconf

[root@dba-01 ~]# yum install make cmake bison-devel ncurses-devel libaio-devel

[root@dba-01 ~]# wget httpss://dl.bintray.com/boostorg/release/1.65.1/source/boost_1_59_0.tar.gz

# Log in to boost Org can also be downloaded
[root@dba-01 ~]# tar xf boost_1_59_0.tar.gz -C /usr/local/

[root@dba-01 ~]# cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.7.20 \
-DMYSQL_DATADIR=/application/mysql-5.7.20/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.7.20/tmp/mysql.sock \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/usr/local/boost_1_59_0 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0 

2, MySQL user permission management

1. Basic MySQL user operations

1. Create user: MySQL > create user bgx;

2. Delete user: delete user drop user
   mysql> drop user bgx; Logical operation
   MySQL > delete user: to delete the property, find the delete from the table

3. Modify user: update
   mysql> update mysql.user set host='127.0.0.1' where user='bgx';
   Query OK, 1 row affected (0.00 sec)
   Rows matched: 1 Changed: 1 Warnings: 0

Note: when updating the password, you must flush privileges, otherwise the changed password will not take effect immediately

2. User definition

1. username @ 'host domain'
2. Host domain: it can be understood as the white list of MySQL login
3. Host domain format:
   '10.0.0.51'
   '10.0.0.5%'
   '10.0.0.%'
   '10.0.%.%'
   '10.%.%.%'
   '%'
   'db01'
   '10.0.0.51/255.255.255.0'

3. User actual combat management

What you should do after installing MySQL database:

1. Set initial password( root@localhost )

[root@dba-01 ~]# mysqladmin -uroot -p password '123'

1. Login with password

[root@dba-01 ~]# mysql -uroot -p123

1. Clean up useless users

4. What if all users are deleted by mistake?

# Step 1: close the database
[root@dba-01 ~]# /etc/init.d/mysqld stop

# Step 2: start the database
[root@dba-01 ~]# mysqld_safe --skip-grant-tables --skip-networking

# Step 3: use MySQL Library
mysql> user mysql;

# Step 4: create root user
mysql> insert into mysql.user values ('localhost','root',PASSWORD('123'),
('Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'Y',
'',
'',
'',
'',0,0,0,0,'mysql_native_password','','N');

5. Forget the root password

# 1. Close the database first
[root@dba-01 ~]# /etc/init.d/mysqld stop

# 2. Start the database
[root@dba-01 ~]# mysqld_safe --skip-grant-tables --skip-networking

# 3. Modify the root password
mysql> update user set password=PASSWORD('oldboy123') where user='root' and host='localhost';

6. User management and authority management

1. Create user

mysql> create user oldboy@'10.0.0.%' identified by '123';

1. View user

mysql> select user,host  from mysql.user;

1. delete user

mysql>  drop user oldboy@'10.0.0.%'；

1. Change Password

mysql> set password
mysql> update user set password=PASSWORD('oldboy123') where user='root' and host='localhost';
mysql> grant all privileges on *.* to oldboy@'10.0.0.%' identified by '123';

7. User permission setting

MySQL permission definition:
Target: Library and table
jurisdiction

INSERT,SELECT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN,  PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE

ascription
Only one owner can be set at a time. There is no concept of group or other users

grant     all privileges    on     *.*    to   oldboy@'10.0.0.%'  identified by    '123';
                jurisdiction               Action object          ascription               password

Action object decomposition:

*. * all tables under all databases in the current MySQL instance
wordress.* All tables in wordpress Library in current MySQL instance (single database level)
wordpress.user the user table in the WordPress Library in the current MySQL instance (single table level)

8. Permission settings in the enterprise

#Generally, create user permissions for developers
grant select,update,delete,insert on *.* to oldboy@'10.0.0.%' identified by '123';

3, MySQL connection management

MySQL
Common client specific connection options:

• -u: Designated user
• -p: Specify password
• -h: Specify password
• -S: Specify the sock
• -e: Specify sql
• --protocol=name: Specifies the connection method

Connection mode

1. socket connection

mysql -uroot -poldboy123 -S/application/mysql/tmp/mysql.sock
mysql -uroot -poldboy123

1. TCP/IP

mysql -uroot -poldboy123 -h10.0.0.51 -P3306

4, MySQL startup and shutdown process

start-up

/etc/init.d/mysqld start ------> mysqld_safe ------> mysqld

close

/etc/init.d/mysqld stop 
mysqladmin -uroot -poldboy123 shutdown
kill -9 pid ?
killall mysqld ?
pkill mysqld ?

Problem:

1. If the business is busy, the database will not release pid and sock files
2. It is claimed that it can achieve the same security as Oracle, but it can not achieve 100%
3. Loss of data in case of busy business (remedial measures, high availability)

5, MySQL instance initialization settings

1. Function of initialization configuration file

Scenario: I want to start the instance

Question:
1) I don't know where my program is?
2) I don't know where to find the database after I start up in the future?
3) Where will I put the startup information and error information when I start in the future?
4) Where is the sock file and pid file when I start?
5) How much memory did you give me when I started?
...
N) I have a lot of questions to tell me before I start, emmmmm

1. Precompiling: cmake to specify and hard code into the program
2. Set the startup initialization configuration on the command line

--skip-grant-tables 
--skip-networking
--datadir=/application/mysql/data
--basedir=/application/mysql
--defaults-file=/etc/my,cnf
--pid-file=/application/mysql/data/db01.pid
--socket=/application/mysql/data/mysql.sock
--user=mysql
--port=3306
--log-error=/application/mysql/data/db01.err

1. Initialization configuration file (/ etc/my.cnf)

Configuration file read order:
/etc/my.cnf
/etc/mysql/my.cnf
$MYSQL_HOME/my.cnf (provided that the MYSQL_HOME variable is defined in the environment variable)
Defaults extra file (similar to include)
~/my.cnf

--Defaults file: the default configuration file

If used/ bin/mysqld_ When the safe daemon starts the mysql database, it uses the -- defaults file = < absolute path of the configuration file > parameter. At this time, only the configuration file specified by this parameter will be used.

reflection:

#cmake: 
socket=/application/mysql/tmp/mysql.sock
#Command line:
--socket=/tmp/mysql.sock
#Profile:
/etc/my.cnf in[mysqld]Under the label: socket=/opt/mysql.sock
#default parameter:
--defaults-file=/tmp/a.txt In the configuration file[mysqld]Under the label: socket=/tmp/test.sock

Priority conclusion:

1. command line
2. defaults-file
3. configuration file
4. precompile

2. Use of initialization configuration file

Initialize profile function:

1. Affect instance startup (mysqld)
2. Affect clients

• mysql
• mysqldump
• mysqladmin

How to configure an initialization profile

1. Configure label classification
   [Client] all client programs
   mysql
   mysqldump
   ...
   [server] all server side
   mysqld
   mysqld_safe
   ...

6, MySQL multi instance configuration

What is multi instance?
(process + multiple threads + allocated memory structure) * multiple

• Multiple sets of background process + thread + memory structure
• Multiple profiles
  • Multiple ports
  • Multiple socket files
  • Multiple log files
  • Multiple servers_ ID file
• Multiple sets of data

actual combat

#Create data directory
[root@db01 ~]# mkdir -p /data/330{7..9}

#create profile
[root@db01 ~]# touch /data/330{7..9}/my.cnf

#Edit 3307 profile
[root@db01 ~]# vim /data/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
log-bin=/data/3307/mysql-bin
server_id=7
port=3307
[client]
socket=/data/3307/mysql.sock

#Edit 3308 profile
[root@db01 ~]# vim /data/3308/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
log-bin=/data/3308/mysql-bin
server_id=8
port=3308
[client]
socket=/data/3308/mysql.sock

#Edit 3309 profile
[root@db01 ~]# vim /data/3309/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
log-bin=/data/3309/mysql-bin
server_id=9
port=3309
[client]
socket=/data/3309/mysql.sock

#Initialize 3307 data
[root@db01 ~]#/application/mysql/scripts//mysql_install_db --user=mysql --defaults-file=/data/3307/my.cnf --basedir=/application/mysql --datadir=/data/3307/data

#Initialize 3308 data
[root@db01 ~]#/application/mysql/scripts//mysql_install_db --user=mysql --defaults-file=/data/3308/my.cnf --basedir=/application/mysql --datadir=/data/3308/data

#Initialize 3309 data
[root@db01 ~]#/application/mysql/scripts/mysql_install_db \
--user=mysql \
--defaults-file=/data/3309/my.cnf \
--basedir=/application/mysql --datadir=/data/3309/data

#Modify directory permissions
[root@db01]# chown -R mysql.mysql /data/330*

#Start multiple instances
[root@db01]# mysqld_safe --defaults-file=/data/3307/my.cnf &
[root@db01]# mysqld_safe --defaults-file=/data/3308/my.cnf &
[root@db01]# mysqld_safe --defaults-file=/data/3309/my.cnf &

#View server_id
[root@db01]# mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
[root@db01]# mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
[root@db01]# mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"