Some common mysql operations in centOS

Source: Internet
Author: User

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
    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 on a specific client and access the specific database fangchandb.
    Mysql> grant select, insert, update, delete, create, drop on fangchandb. * to custom @ 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.