Start installation:

sudo apt-get install mysql-common mysql-server

Simple use

Building database

CREATE DATABASE IF NOT EXISTS yourdbname DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

Building tables

create table MyClass(id int(4) not null primary key auto_increment,name char(20) not null,sex int(4) not null default '0',degree double(16,2));

increase

insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);

Delete

delete from MyClass where id=1;

change

update MyClass set name='Mary' where id=1;

check

select * from MyClass;

Display all view s

select * from information_schema.TABLES where table_type='view' AND table_schema = 'Database name';

Further operation

Create users:

create user xxx identified by 'password';

Rename:

rename user aaa to bbb；

Delete user:

drop user aaa;

Display permissions:

Show grants for AAA (user);

Grant authority:

Grant select on XXX (database). * to AAA (user);

Grant full privileges to a database:

Grant all on XXX (database). * to AAA (user);
Grant all on XXX (database). * to AAA (user)@localhost;

Cancellation of authorization:

revoke all on *.* from aaa(user)@localhost;

Modify authority

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'   WITH GRANT OPTION;

Remember to refresh permissions after the above operations are completed:

flush privileges;

Import and export

Export data and table structure:

Mysqldump-uroot-p ABC (database name) > abc.sql
 Enter the password after typing in the car

Export table structure only

mysqldump -uroot -p -d abc > abc.sql

Import database
1. Build empty database first

mysql> create database abc;

2. Importing database

Mysql-u root-p ABC (database name) < abc.sql

Automatic backup of database

New backup script xxx.sh, enter the following

#!/bin/bash

# The name of the database to be backed up, with multiple databases separated by spaces
databases=("db1", "db2") 

# Backup files to save directories, note that the current user must save directory read and write permissions
basepath='/root/backup/mysql/'

if [ ! -d "$basepath" ]; then
  mkdir -p "$basepath"
fi

# Loop databases array
for db in ${databases[*]}
  do
    # Backup database to generate SQL file
    nice -n 19 /usr/bin/mysqldump -uroot -pcd32d5e86e --database $db > $basepath$db-$(date +%Y%m%d).sql
    
    # Compression of generated SQL files
    nice -n 19 tar zPcf $basepath$db-$(date +%Y%m%d).sql.tar.gz -C $basepath $db-$(date +%Y%m%d).sql
    
    # Delete backup data 7 days ago
    find $basepath -mtime +7 -name "*.sql.tar.gz" -exec rm -rf {} \;
  done

  # Delete the generated SQL file
  rm -rf $basepath/*.sql

Use crontab to set the timed task, input crontab-e in the terminal, add the following content, this task is automatically executed at 3:00 a day.

03 * * * bash xxx. sh (fill in the absolute address of the script here)

Open Logging

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 1000M
binlog-format = row

Safe mode operation

Enter Security Mode to Modify Password

mysqld_safe --skip-grant-tables &

select user,host,password from user where user="root"

Different versions of mysql modify user passwords in different ways, you need to look at the password field in mysql - > user, if not password, then authentication_string.

authentication_string is modified differently:

use mysql;
update user set authentication_string=PASSWORD("") where User='root';
update user set plugin="mysql_native_password";
flush privileges;
quit;
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

If it is not authentication_string, the following method can be used.

update user set password=PASSWORD("your_password") where user="root" and host="localhost"

New operation

Mysql Full Text Search

Create index

create fulltext index ngram_idx on tag(Title) with parser ngram;
//or
alter table tag add fulltext index ngram_idx(Title) with parser ngram;

Minimum participle length for support

SHOW VARIABLES LIKE 'ft_min_word_len';
//unix system can be modified in / etc/my.cnf
[mysqld]
ft_min_word_len = 1

Start using

select Title,match(Title) against('Clean water') from tag ;

Problem sets that may arise:

• describe

2017-05-04T01:21:32.004560Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-05-04T01:21:32.023009Z mysqld_safe A mysqld process already exists

Solution:

$ sudo killall mysqld

• describe

2017-05-04T01:22:26.486677Z mysqld_safe Logging to '/var/log/mysql/error.log'.
2017-05-04T01:22:26.488204Z mysqld_safe Directory '/var/run/mysqld' for UNIX socket file don't exists.

Solution:

sudo mkdir -p /var/run/mysqld
sudo chown -R mysql:mysql /var/run/mysqld

• describe

$ sudo /etc/init.d/mysql start
ies: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
[....] Starting mysql (via systemctl): mysql.servicejob-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

Solution:

The current folder is not caused by the actual directory
 cd to an actual directory location

• describe

sudo /etc/init.d/mysql start
shell-init: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
job-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
[....] Starting mysql (via systemctl): mysql.servicejob-working-directory: error retrieving current directory: getcwd: cannot access parent directories: No such file or directory
Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

Follow the tips: See "system CTL status mysql. service" and "journalctl - xe" for details.
But what's the use of mysql? Look directly at mysql's log:/var/log/mysql/error.log
2017-05-04T01:37:56.583745Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.

Solution:

Kill all mysqld processes: kill all mysqld 
Suddeo/etc/init.d/mysql start again succeeded

• describe

dpkg is locked

Solution

sudo rm /var/cache/apt/archives/lock
sudo rm /var/lib/dpkg/lock