Common MySQL operations in CentOS

Install MySQL
Yum-y Install Mysql-server

2. Modify MySQL Configuration
   VI/ETC/MY.CNF There will be a lot of configuration items to be aware of, followed by special notes
   Temporarily modify the code (added below the password): Default-character-set = UTF8
4. Set up MySQL to boot with the system
   # chkconfig mysqld on← set up MySQL service with system boot self-boot
   # chkconfig--list mysqld← confirm MySQL self-booting mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off← If 2--5 is on the status OK
   #/etc/rc.d/init.d/mysqld start← start MySQL service
   
   
6. Show current MySQL version and current date
   Select version (), current_date;
   
   
8. Modify the MySQL root password
   # mysql-u root← log in to MySQL server with root user
   Select User,host,password from Mysql.user; ← Viewing user Information
   Set password for [email Protected]=password (' Enter root password here '); ← Set Root Password
   Select User,host,password from Mysql.user; ← Viewing user Information
   exit← exiting the MySQL server
   
   
10. Log in to MySQL with a password
    Mysql-u root-p
12. Remove MySQL anonymous user
    Select User,host from Mysql.user; ← Viewing user Information
    Delete from mysql.user where user= '; ← Deleting anonymous users
    Select User,host from Mysql.user; ← Viewing user Information
14. View Database
    show databases; ← Viewing a database that already exists on the system
    drop database test; ← Deleting an empty database named Test
    show databases; ← Viewing a database that already exists on the system
    
    MySQL view open port: Show variables like ' Port ';
16. Create a new user and authorize a new user
    Grant all privileges the test.* to [e-mail protected] identified by ' Defining the password here '; ← Establish a user named Centospub that has full operational permissions on the test database
    
    Create a full superuser who can connect to the server from anywhere, but must use a password
    Mysql> Grant all privileges on * * to [e-mail protected] identified by ' password '
    Add new users
    Format:
    Grant SELECT on database. * To User name @ login host identified by "password"
    GRANT all privileges on * * to [email protected] identified by ' something ' with GRANT OPTION;
    GRANT all privileges on * * to [e-mail protected] "%" identified by ' something ' with GRANT OPTION;
    Remove Authorization:
    Mysql> revoke all privileges on * * FROM [email protected] "%";
    mysql> Delete from user where user= "root" and host= "%";
    mysql> flush Privileges;
18. Fine-grained delegation of authority
    Create a user custom login on a specific client it363.com to access a specific database fangchandb
    MySQL >grant Select, insert, UPDATE, Delete, Create,drop on fangchandb.* to [e-mail protected] it363.com identified by ' passwd
    
    
20. Create a new database
    Create DATABASE test; ← Create a database named Test (note whether it is possible to establish this database is determined when a new user is created above)
22. Working with databases
    Use test← Connect to database
    Show tables; ← Viewing tables that already exist in the database
24. Delete a test account
    Revoke all privileges on * * FROM [email protected]; ← Cancel centospub User permissions on database operations
    Delete from Mysql.user where user= ' centospub ' and host= ' localhost '; ← Delete centospub Users
    Select User from Mysql.user where user= ' centospub '; ← Find User centospub, confirm deleted or not
    Flush privileges; ← Refresh to make the above actions effective
26. Deleting a database
    Drop database name deletes databases directly and does not alert
    Mysqladmin drop DatabaseName You are prompted before you delete the database.
28. Table Operations
    Show tables; Show Table
    Describe TableName; Detailed description of the table
    Renaming tables: mysql > ALTER table t1 rename T2;
    
    
30. Mysqldump in the CentOS system
    Execute the following command in the shell
    backing up the database shell> mysqldump-h yourhost vi-u root-p dbname >dbname_backup.sql
    recovering a database shell> mysqladmin-h yourhost-u root-p Create dbname
    shell> mysqldump-h yourhost-u root-p dbname < Dbname_backup.sql
    If you only want to dump the table directive, the command is as follows: Shell> mysqladmin-u root-p-D databasename > A.sql
    If you only want to dump the SQL command that inserts the data and do not need to create a table command, the command is as follows: shell> mysqladmin-u root-p-t databasename > A.sql
    So what should I do if I only want the data and I don't want any SQL commands? mysqldump-t./Phptest Driver
    Only the-t parameter is specified to unload the plain text file, which represents the directory where the data is unloaded./represents the current directory, that is, the same directory as mysqldump. If you do not specify a driver table, the data for the entire database is unloaded. Each table generates two files, one for the. sql file, which contains the build table execution. The other is a. txt file that contains only data and no SQL instructions.
    
    
32. You can store the query in a file and tell MySQL to read the query from the file instead of waiting for keyboard input.
    The input redirection utility can be used to accomplish this task. For example, if you have queries in file My_file.sql, you can execute these queries as follows:
    If you want to write the statement in advance in sql.txt: mysql > mysql-h yourhost-u root-p yourdatabase < Sql.txt

Common MySQL operations in CentOS