MySQL Common Operation Command Learning

Objective: [this learning exercise allows you to install the MySQL database using the RPM package, and manage, authorize, and back up the MySQL database.]

Outline:
1. Connect to MySQL
Ii. MySQL management and authorization
3. Simple database operations
Iv. Database Backup
5. Postscript
1. Install and connect to MySQL
First check whether MySQL is installed:
Rpm-Qa | grep mysql-I (I am using the RPM package method to check whether this package is installed)
You can use RPM or tarball to install the tool. For more information, see Google.

Format: mysql-H remote host address-u username-P press ENTER
Enter the password:

Mysql-u root-P press ENTER
Enter Password: enter the password
Mysql> enter
Exit command:> exit or Ctrl + d

Ii. MySQL management and authorization
1. Change the password:
Format: mysqladmin-u username-P old Password New Password

2. Add new users:
> Grant create, select, update... (grant related operation permissions)
-> On database .*
-> To username @ login host identified by 'Password'

Operation instance:

Add a password to the root user:
# Mysqladmin-u Root Password 52 netseek
The old-P password can be omitted because the root user does not have a password.
Login test:
# Mysql-u root-P press ENTER
Enter the password and log on successfully.

Change the original MySQL logon password 52netseek to 52china.
# Mysqladmin-u root-P 52 netseek password '52china'

Create a database, add users, and grant corresponding permissions:
Mysql> Create Database phpBB;
Query OK, 1 row affected (0.02 Sec)

Mysql> Use phpBB;
Database changed
Mysql> grant create, select, update, insert, delete, alter
-> On phpBB .*
-> To phpbbroot @ localhost identified by '52netseek ';
Query OK, 0 rows affected (0.00 Sec)

Grant all permissions:
> Grant all privileges
> On BBS .*
> To bbsroot @ localhost identified by '52netseek'

Revoke permissions:
Revoke create, select, update, insert, delete, alter
On phpBB .*
From phpbbroot @ localhost identified by '52netseek ';

Delete the user phpbbroot completely:
> Use MySQL
> Delete from user
Where user = 'phpbbroot 'and host = 'localhost ';
> Flush privileges; refresh the database

3. Simple Database Operations
1. display the Database List:
show databases;
mysql
test
2. make it the current operating database
use MySQL; open the database.
show tables; displays the data tables in the MySQL database.
3. display the table structure of the data table:
describe table name;
describe user; display the table structure of the User table:
4. create a database and create a table
Create Database database name;
Use Database Name;
Create Table Name (field setting list)
5. delete a database, excluding tables
drop database database name;
drop table name;
6. displays records in the Table.
select * from table name;
7. modify database structure:
Add field:
alter table dbname add column
Modify Field:
alter table dbname change
Delete field:
alter table dbname drop column

instance operation:
Create Database Office;
use office;
mysql> Create Table personal (
-> member_no char (5) not null,
-> name char (,
-> birthday date,
-> exam_score tinyint,
-> Primary Key (member_no)
->);
query OK, 0 rows affected (0.01 Sec)
DESC personal; display table structure:
+ ------------ + ------ + ----- + --------- + ------- +
| FIELD | type | null | key | default | extra |
+ ------------ + ------ + ----- + --------- + ------- +
| member_no | char (5) | pri |
| Name | char (| Yes | null |
| birthday | date | Yes | null |
| exam_score | tinyint (4) | Yes | null |
+ ------------ + ---------- + ------ + ----- + --------- + ------- +
4 rows in SET (0.00 Sec)

Insert into personal values ('001', 'netsecret', '2017-03-15 ', '95 ');
Insert into personal values ('002 ', 'heihei', '2017-02-24', '90 ');
Insert into personal values ('003 ', 'gogogo', '2017-05-21', '85 ');
Insert into personal values ('004 ', 'hahaha', '2017-02-25', '84 ');
Insert into personal values ('005 ', 'linlin', '2017-04-28', '85 ');
Insert into personal values ('006 ', 'xinxin', '2017-03-15 ', '75 ');

Mysql> select * from personal;
+ ----------- + --------- + ------------ +
| Member_no | Name | birthday | exam_score |
+ ----------- + --------- + ------------ +
| 001 | netseek | 1983-03-15 | 95 |
| 002 | Heihei | 1982-02-24 | 90 |
| 003 | Gogo | 1985-05-21 | 85 |
| 004 | Haha | 1984-02-25 | 84 |
| 005 | Linlin | 1982-04-28 | 85 |
| 006 | xinxin | 1985-03-15 | 75 |
+ ----------- + --------- + ------------ +

Modify database table:
Requirement: Add a field of height after birthday, whose data type is tinyint.
Rename the exam_score field to scores, and the Data Type remains unchanged.
> Alter table personal
-> Add column height tinyint after birthday,
-> Change column exam_score scores tinyint;

Mysql> select * from personal;
+ ----------- + --------- + ------------ + -------- +
| Member_no | Name | birthday | height | scores |
+ ----------- + --------- + ------------ + -------- +
| 001 | netseek | 1983-03-15 | null | 95 |
| 002 | Heihei | 1982-02-24 | null | 90 |
| 003 | Gogo | 1985-05-21 | null | 85 |
| 004 | Haha | 1984-02-25 | null | 84 |
| 005 | Linlin | 1982-04-28 | null | 85 |
| 006 | xinxin | 1985-03-15 | null | 75 |
+ ----------- + --------- + ------------ + -------- +

Insert data to the table:
> Update personal set scores = 95 + 5 where name = 'netsecret ';
> Select scores from personal where name = 'netsecret ';
+ -------- +
| Scores |
+ -------- +
| 1, 100 |
+ -------- +

Delete all the information in the table named 'gogogo:
> Delete from personal where name = 'gogogo ';

Remove tables from the database:
Mysql> drop table if exists personal;

Iii. Import and export Databases
Export:
Use the select into OUTFILE 'filename' statement
Practical Use of mysqldumpProgram
Use the select into OUTFILE 'filename' statement

1. Only one table can be processed, and only data in the output file, without table structure
Assume that officehas a table named "personal", and now we need to dismount the" personal" file into the "out.txt" file:
> Use office;
> Select * from personal into OUTFILE 'out.txt '; you can see out.txt under/var/lib/MySQL/office/directory.
Select * from personal into OUTFILE './out.txt'; you can see that out.txt is used in the/var/lib/MySQL/directory of out.txt.

2. Use the mysqldump utility (you can easily process multiple tables)
# Cd/var/lib/MySQL
Export the table creation and insertion commands for creating related tables
# Mysqldump BBS> bbs. SQL import the database BBS to BBs. SQL

If you want to import bbs. SQL to the database, you can use:
Mysql> Create Database bbstest; Create a database named office first.
# MySQL bbstest <bbs. SQL (this is often used to upload local database files to the server and then import them to the database)

Just want to export the table creation command:
# Mysqldump-d bbs> bbscreate. SQL
SQL commands that only want to export the inserted data:
# Mysqldump-t bbs> bbsinsert. SQL
Export the database table creation commands and data in the table at the same time:
# Mysqldump-T./BBS cdb_admingroups (where./indicates the current directory and cdb_admingroups is a table in the BBS database)
# Ls
Cdb_admingroups. SQL export the table creation command
Cdb_admingroups.txt exports table data

Import:
Load a database from a file:
Mysql> load data infile "/tmp/name.txt" into table names;
Mysql> select * from names;

Iv. Database Backup

1. manually copy backup:
MySQL database files are stored in the/var/lib/MySQL directory. The database creates a directory for each database. All database files are stored in these directories.
[Root @ linuxhero MySQL] # ls
BBS MySQL mysql. Sock phpBB test office displays the databases.

If we want to back up the current database directory as MySQL. Bak.
[Root @ linuxhero lib] # cp-RF MySQL mysql. Bak
If the database is damaged, you need to restore the database:
[Root @ linuxhero lib] # cp-RF mysql. bak/* MySQL
After the database is restored, the files in var/lib/MySQL have been changed. To change the permissions of the files, you must change the user's read and write permissions of the MySQL database.
So we have to start and run MySQL and log on to the database:
[Root @ linuxhero lib] #/etc/init. d/mysqld start
[Root @ linuxhero lib] # mysql-u root-P
Enter Password: enter the password to log on.
Mysql> show databses;

2. Use mysqldump to back up the database
[Root @ linuxhero MySQL] # mysqldump -- opt BBS-u root-P> BBs. SQL
Enter password:
Note: Other Options for adding backup with -- opt, where BB is one of the database names,
The above indicates that the backup is written to the file BB. SQL using the redirection output.
[Root @ linuxhero MySQL] # less bbs. SQL

To restore the database BB, perform the following operations:
[Root @ linuxhero MySQL] # MySQL BBS-u root-P <BBs. SQL

To back up all databases:
[Root @ linuxhero MySQL] # mysqldump -- Opt -- all-databases-u root-P> MySQL. Bak
Enetr password: enter the password.
Recover all databases without entering the Database Name:
[Root @ linuxhero MySQL] # mysql-u root-P <MySQL. Bak
Enetr password: enter the password.

V. Appendix: What should I do if I forget my MySQL password?
If MySQL is running, killall-term mysqld is first killed.
Start MYSQL: Bin/safe_mysqld -- skip-grant-tables &
You can access MySQL without a password.
Then
> Use MySQL
> Update user SET Password = PASSWORD ("new_pass") where user = "root ";
> Flush privileges;
Kill MySQL again and start MySql in a normal way.