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

Let's talk about how to manage MySQL tables. The secret of the text is that it is relevant to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article on how to manage MySQL tables.

MySQL

Data import

Store the contents of the system files in the table of the database

/ etc/passwd studb.user

User name password placeholder uid gid description information home directory shell

Creat database studb

Create table studb.user (

Name varchar (50)

Password char (1)

Uid int (2)

Gid int (2)

Comment varchar (100)

Homedir char (100)

Shll char (25)

Index (name)

);

Import data format:

Msyql > load data infile "directory / file name" into table library. Table name fields terminated by "Field interval symbol" lines terminated by "\ n"

Check the default usage directory and whether it exists

Mysql > show variables like "secure_file_priv"

+-+

| | Variable_name | Value |

+-+

| | secure_file_priv | / var/lib/mysql-files/ |

+-+

1 row in set (0.00 sec)

Copy the data information into the default usage directory

Cp / etc/passwd / var/lib/mysql-files/

Ls / var/lib/mysql-files/

Load data infile "/ var/lib/mysql-files/passwd" into table user fields terminated by ":" lines treminated by "\ n"

Alter table studb.user add id int (2) primary key auto_increment first; add self-increasing line number

Modify the catalog and view the modification results

Mkdir / myfile

Chown mysql / myfile gives permission to change the owner to mysql

Vim / etc/my.cnf modifies the configuration file, using directories by default

[mysqld]

Secure_file_priv= "/ myfile"

Data export: storing table records in system files

Into outfile "directory name / file name" [fields terminated by "symbol" lines terminated by "symbol]

Eg:

Mysql > select name,uid from user into outfile "/ myfile/user1.txt"

Mysql > select name,uid from user into outfile "/ myfile/user2.txt" fields terminated by "#" set up an interval number between exported files #

Mysql > select name,uid from user limit 5 into outfile "/ myfile/user3.txt" the first five lines

Mysql > select id,name from user limit 5 into outfile "/ var/lib/mysql-files/4.txt" lines terminated by ":"

Manage table records

Increase

Insert into library. Table values (list of field values)

Insert into library. Table values (field values list), (field values list)

Check

Select field name list from library. Table

Select field name list from library. Table where condition

Eg:select * from user where name= "mysql"; find the records of all name=mysql in the user table

Single table query

Representation of the condition match:

Numerical comparison > > =

< update mysql.user set authentication_string=password("888888") ->

Where user= "root" and host= "localhost"

There is a password record of the user's login information in the mysql.user table, which can be changed.

Mysql > flush privileges; refresh

Modify the configuration file after exiting mysql to delete skip permissions

User Authorization grant

Add a new connection user to the database cloud server

Mysql > grant permission list on library name. Table name to user @ "client address" identified by "password" [with grant option can be added so that new users have the right to add users]

Grant all on.

When the name of the library. The table name is. Matches all libraries and all tables when

Authorization settings are placed in the user table of the mysql library

Grant all on. To root@ "192.168.4.12" identified by "123456" with grant option; (authorized for 192.168.4.12, user root, password 123456)

Database CVM IP is 192.168.4.11

The new virtual machine mysql12,IP is 192.168.4.12

Client test authorization:

# which mysql

# yum-y install mariadb

Mysql >-h IP address of database CVM-u user name-p password

Mysql > select @ @ hostname; to view the currently logged in data CVM

Mysql > select user (); view the terminal currently being accessed

Mysql > show grants; to view your permissions

Allow the use of bbsuser users to connect from the CVM of the website. The password is 123456. You only have full permissions for all tables under the bbsdb library, but no authorization permissions.

Grant all on bbsdb.* to bbsuser@ "192.168.4.30" identified by "123456"

Only 192.168.4.30 can log in using the user bbsuser, and the bbsdb library can only be added, deleted and modified in the database.

Grant select on. To admin@ "localhost" identified by "123456"

Native user admin only has read permission

Administrator to view other user permissions

Show grants for user name @ client address

Permission revocation revoke

Mysql > revoke permission list on library name. Table name from user name @ client address

Revoke record information

Revoke delete,update on. From user name @ client address

Delete authorized user drop user username @ client address

Use mysql

Show tables

User already has authorized user information

Db authorizes user access to the library

Data backup

1 Why back up the data?

Use backup files to restore data when data is lost or mistakenly deleted.

2 data backup mode?

Physical backup? Backup database or table corresponding file

Cp-r / var/lib/mysql/mysql / opt/mysql.bak

Cp / var/lib/mysql/mysql/user.* / opt/

Tar-zcvf / opt/mysql.tar.gz / var/lib/mysql/mysql/*

164cp-r / mydata/mysql.bak/ / var/lib/mysql/mysql

165 chown-R mysql:mysql / var/lib/mysql/mysql

166 systemctl restart mysqld

Logical backup? When backing up, the corresponding sql command is generated according to the existing database tables and records, and the

Sql is saved to the specified backup file

3 data backup strategy?

Full backup backs up all data (one CVM, one database, one table)

Differential backup all new backups after full backup

Incremental backups all new backups since the last backup

Full backup + differential backup

Full backup + incremental backup

4 how to realize data backup in the generation environment

Periodically schedule tasks to execute backup scripts

00 18 1 sh / shell/allbak.sh

5 factors to be considered when backing up data?

Backup mode logical backup

Backup strategy? Complete difference increment

How often is the data backed up? 1 hour, 1 day, 1 week

When is the data backed up? Perform a backup when the data access volume is small

Is the storage space scalable? LV

Should backup files be named with identification? Use date as backup file name

Full backup

# mysqldump-hlocalhost-uroot-p123qqq database name

Directory name / name .sql

How to represent the database name?

-- all-databases backs up all data on a service

Database name backs up all tables in a library

Database name table name back up all the data in a table

-B database name 1 database name 2 database name N backup all data from certain libraries

# mkdir / databak

# mysqldump-uroot-p123qqq userdb >

/ databak/userdb.sql

# mysqldump-uroot-p123qqq teadb >

/ databak/teadb.sql

Complete recovery

# mysql-hlocalhost-uroot-p123qqq database name

< 目录 名/名.sql mysql>

Drop database teadb

Mysql > create database teadb

# mysql-uroot-p123qqq teadb

< /databak/teadb.sql mysql>

Use teadb; show tables

# crontab-e

00 18 1 / opt/teadbbak.sh & > / dev/null

00 18 2-7 / opt/baknewbinlogfile.sh

Vim / opt/baknewbinlogfile.sh

#! / bin/bash

Back up the newly generated binlog log files every day and the binlog log texts in use

Parts are not backed up

: wq

Vim / opt/teadbbak.sh

#! / bin/bash

If [!-e / databak]; then

Mkdir / databak

Fi

Day=date +% F

Mysqldump-uroot-p123qqq-flush-logs teadb >

/ databak/teadb-$ {day} .sql

: wq

Chmod + x / opt/teadbbak.sh

Disadvantages of backing up data using only the full backup strategy:

A when you restore data using a full backup file, you can only restore the data to the state it was in at the time of backup.

The newly generated data cannot be recovered from a full backup

B write locks are added to the table when backing up and restoring data.

+ + two

, incremental backup (enable mysql service binlog log to do regular incremental backup, installation

Third-party software provides incremental backup commands for backup)

2.1 enable mysql service binlog logs to make incremental backups

Binlog log, also known as binary log, is the log file of mysql data service.

One that records the sql other than queries executed after the client connects to the database service

Orders.

Mysql-hx.x.x.x-uroot-p123456

Mysql > select desc show tables

Mysql > create insert update delete grant revoke

Enable binlog Log

Mysql > show variables like "binlog_format"

Vim / etc/my.cnf

[mysqld]

Server_id=12

Log_bin

Binlog_format= "mixed"

: wq

# systemctl restart mysqld

Mysql > show variables like "binlog_format"

Ls / var/lib/mysql/ hostname-bin.000001 500M+

Ls / var/lib/mysql/localhost-bin.index index file

View the contents of the binlog log file

# mysqlbinlog / var/lib/mysql/localhost-bin.000001

How does the binlog log file record sql commands?

Time point

-start-datetime= "yyyy-mm-dd hh:mm:ss"

-stop-datetime= "yyyy-mm-dd hh:mm:ss"

Pos point

-- start-position= number

-- stop-position= number

Execute the sql command in the binlog log to recover data

# mysqlbinlog [option] log file name | mysql-uroot-

P123qqq

# mysqlbinlog-start-position=300-stop-

Position=1006 / var/lib/mysql/localhost-bin.000001 |

Mysql-uroot-p123qqq

Manually generate new binlog logs?

Mysql > flush logs

Mysql-uroot-p123qqq-e "flush logs" systemctl restart mysqld

# mysqldump-uroot-p123qqq-- flush-logs teadb T7 >

/ databak/t7.sql

Delete existing binlog log files

Mysql > reset master

Mysql > purge master logs to "binlog File name"

# rm-rf binlog log file

Customize the directory and file name of the binlog log file store

Mkdir / logdirchown mysql / logdirsetenforce 0

# vim / etc/my.cnf

Server_id=12

# log_bin

Log_bin=/logdir/plj

Binlog_format= "mixed"

: wq

# systemctl restart mysqld

# ls / logdir/

+ +

2.3 install third-party software percona provides incremental backup commands for backup

A powerful online hot backup tool

The library table is not locked during backup, which is suitable for production environment.

Provided by professional organization Percona (improved MySQL branch)

It mainly contains two components.

Xtrabackup:C program that supports InnoDB/XtraDB

Innobackupex: encapsulates xtrabackup in Perl scripts, and also supports

MyISAM

# yum-y install perl-DBD-MySQL perl-Digest-MD5

# rpm-ivh libev-4.15-1.el6.rf.x86_64.rpm

Rpm-ivh percona-xtrabackup-24-2.4.7-

1.el7.x86_64.rpm

Rpm-qa | grep perconarpm-ql percona-xtrabackup-24

# man innobackupex

# man xtrabackup

# innobackupex

1 innobackupex full backup and recovery

# mkdir / pljdir

Innobackupex-user root-password 123qqq-

Databases= "teadb" / pljdir-- no-timestamp

Innobackupex-user root-password 123qqq-

Databases= "teadb"-- apply-log / pljdir

Complete recovery

Cp-r / var/lib/mysql/mysql / opt/mysql.bak

# rm-rf / var/lib/mysql

# mkdir / var/lib/mysql

Innobackupex-user root-password 123qqq-

Databases= "teadb"-- copy-back / pljdir

Cp-r / opt/mysql.bak / var/lib/mysql/mysqlchown-R mysql:mysql / var/lib/mysql

# systemctl restart mysqld

Description of configuration file under backup directory

Backup-my.cnf

Xtrabackup_checkpoints

Xtrabackup_logfile

Ibdata1

Database / var/lib/mysql/

Transaction log file

Lsn log serial number

Ib_logfile0

Ib_logfile1

Ibdata1

Reinitialize the initial data in the database directory

# systemctl stop mysqld

# rm-rf / var/lib/mysql

Vim / etc/my.cnf

[mysqld]

# validate_password_policy=0

# validate_password_length=6

: wq

# mysql_install_db-datadir=/var/lib/mysql--

User=mysql

# ls / var/lib/mysql/

# rm-rf / var/lib/mysql/mysql

# cp-r / opt/mysql.bak / var/lib/mysql/mysql

# chown-R mysql:mysql / var/lib/mysql/mysql

# systemctl start mysqld

2 innobackupex incremental backup

Full backup of db101.t1 4murmur999

# innobackupex-user root-password 123456-

Databases= "db101.t1" / fullbak-- no-timestamp

First incremental backup 8888

# innobackupex-user root-password 123456-

Databases= "db101.t1"-incremental / new1dir-

Incremental--basedir=/fullbak-no-timestamp

Second incremental backup 7777

# innobackupex-user root-password 123456-

Databases= "db101.t1"-incremental / new2dir-

Incremental--basedir=/new1dir-no-timestamp

Incremental recovery step

1 rm-rf / var/lib/mysql/

2 recover log information

3 recover data

4 restart the database service

5 Log in to view

Incremental recovery step

1 rm-rf / var/lib/mysql/

2 mkdir / var/lib/mysql

2 recover log information

# innobackupex-user root-password 123456-

Databases= "db106.t1"-apply-log-redo-only / onedir

# innobackupex-user root-password 123456-

Databases= "db106.t1"-apply-log-redo-only / onedir

-- incremental-dir= "/ dir2"

# innobackupex-user root-password 123456-

Databases= "db106.t1"-apply-log-redo-only / onedir

-- incremental-dir= "/ dir3"

3 recover data

# innobackupex-user root-password 123456-

Databases= "db106.t1"-- copy-back / onedir

4 restart the database service

# cp-r / root/mysql.plj / var/lib/mysql/mysql

# systemctl start mysqld

# chown-R mysql:mysql / var/lib/mysql

# systemctl stop mysqld

# systemctl start mysqld

5 Log in to view

Mysql-uroot-p123456

Mysql > select * from db1.t1

+ +

3 restore the records of a table using a full backup file.

Db106.a/b/t1

Full backup

# innobackupex-user root-password 123456-databases= "db106" / db106all-no-timestamp

# ls / db106all

# mysql-uroot-p123456

# drop table db106.a

Restore the record of a table

# innobackupex-user root-password 123456-databases= "db106"-apply-log-export / db106all

# ls / db106all/a.*

Mysql > create table db106.a (id int)

Mysql > alter table db106.a discard tablespace

Mysql > system cp / db106all/db106/a. {ibd,cfg,exp} / var/lib/mysql/db106/

Mysql > system chown mysql:mysql / var/lib/mysql/db106/a.*

Mysql > alter table db106.an import tablespace

Mysql > select * from db106.a

Is there anything you don't understand about how to manage the MySQL table above? Or if you want to know more about it, you can continue to follow our industry information section.

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.