Some common mysql operations in centOS

Some common mysql operations in centOS
Install mysql
Yum-y install mysql-server

1. Modify mysql configurations
   Vi/etc/my. cnf there will be a lot of configuration items to pay attention to, there will be special notes later
   Modify the encoding temporarily (under the password): default-character-set = utf8
2. Set mysql to start with the System
   # Chkconfig mysqld on startup
   # Chkconfig -- list mysqld restart confirm MySQL self-starting mysqld 0: off 1: off 2: on 3: on 4: on 5: on 6: off slave if 2--5 is on status OK
   #/Etc/rc. d/init. d/mysqld start slave start MySQL Service
   
3. Display current mysql version and current date
   Select version (), current_date;
   
4. Modify the mysql root Password
   # Mysql-u root login use the root user to log on to the MySQL server
   Select user, host, password from mysql. user; login view user information
   Set password for root @ localhost = password ('fill in hereRoot Password '); configure the root password
   Select user, host, password from mysql. user; login view user information
   Exit from MySQL Server
   
5. Log on to mysql with a password
   Mysql-u root-p
6. Delete an anonymous mysql user
   Select user, host from mysql. user; users to view user information
   Delete from mysql. user where user = ''; anonymous deletes Anonymous Users
   Select user, host from mysql. user; users to view user information
7. View Database
   Show databases; databases
   Drop database test; Delete empty database named test
   Show databases; databases
   
   View the opened port in mysql: show variables like 'Port ';
8. Create and authorize a new user
   Grant all privileges on test. * to centospub @ localhost identified by 'define the password' here; Revoke creates a user named centospub who has full operation permissions on the test database.
   
   Create a full super user who can connect to the server from anywhere, but must use a password
   Mysql> grant all privileges on *. * to user @ localhost identified by 'Password'
   Add new users
   Format:
   Grant select on database. * to username @ login host identified by "password"
   Grant all privileges on *. * TO monty @ localhost identified by 'something' with grant option;
   Grant all privileges on *. * TO monty @ "%" identified by 'something' with grant option;
   Delete authorization:
   Mysql> revoke all privileges on *. * from root @ "% ";
   Mysql> delete from user where user = "root" and host = "% ";
   Mysql> flush privileges;
9. Fine-grained authorization
   Create a User custom to log on to it363.com on a specific client and access the specific database fangchandb.
   Mysql> grant select, insert, update, delete, create, drop on fangchandb. * to custom @ it363.com identified by 'passwd'
   
10. Create a new database
    Create database test; create a database named test (Note whether this database can be created depends on the creation of a new user)
11. Use Database
    Use test cases to connect to the database
    Show tables; tables view existing tables in the database
12. Delete test account
    Revoke all privileges on *. * from centospub @ localhost; revoke cancels the database operation permissions of centospub users.
    Delete from mysql. user where user = 'centospub 'and host = 'localhost'; then delete the centospub user
    Select user from mysql. user where user = 'centospub '; locate the user centospub and check whether the user has been deleted.
    Flush privileges; volume refresh to make the above operations take effect
13. Delete Database
    Drop database name directly deletes the database, no reminder
    Before mysqladmin drop databasename deletes a database, a message is displayed.
14. Table operations
    Show tables; displays tables
    Describe tablename; Detailed description of the table
    Rename a table: mysql> alter table t1 rename t2;
    
15. Mysqldump in CentOS
    Execute the following command in shell
    Back up database shell> mysqldump-h yourhost vi-u root-p dbname> dbname_backup. SQL
    Restore 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 run the Dump command, run the following command: shell> mysqladmin-u root-p-d databasename> a. SQL
    If you only want to Dump the SQL command for data insertion without the table creation command, the command is as follows: shell> mysqladmin-u root-p-t databasename> a. SQL
    What should I do if I only want data and do not want any SQL commands? Mysqldump-T./phptest driver
    In this example, only the-T parameter can be specified to unload a plain text file, indicating the directory for unloading data and./indicating the current directory, that is, the same directory as mysqldump. If no driver table is specified, the data of the entire database is detached. Each table generates two files, one of which is a. SQL file, including table creation and execution. The other is a. txt file that only contains data and does not contain SQL commands.
    
16. You can store the query in a file and tell mysql to read the query from the file instead of waiting for keyboard input.
    You can use the input redirection utility to do this. For example, if the file my_file. SQL contains queries, You can execute the following queries:
    If you want to write the TABLE statement in SQL .txt: mysql> mysql-h yourhost-u root-p yourdatabase <SQL .txt