Mysql practice-level commands for beginners

Source: Internet
Author: User
Mysql practice level: mysql beginners common command Creation Time: 4.04.08.24 modification time: 2014.09.26 from the perspective of an O & M engineer and DBA novice, learn and practice to master mysql related operations. 1. Login

Mysql practice level: mysql beginners common command Creation Time: 4.04.08.24 modification time: 2014.09.26 from the perspective of an O & M engineer and DBA novice, learn and practice to master mysql related operations. 1. Login

Mysql practice-level commands for beginners

Creation Time:

Modification time:

From the perspective of a novice O & M engineer and DBA, learn and practice mysql-related operations.

1. log on to the MySQL server

When facing a running mysql service, the first thing we need to do is log on to the mysql service.

The MySQL client can connect to the mysqld server in two different ways:

  • Non-Standard Port Logon

    Non-3306 service port logon, for example:

    Mysql-u qunyingliu-p 51cto20140824-P 3307 mysql-h 10.1.8.24-u qunyingliu-p 51cto20140824-P 3307
  • B. Connect to the mysql service through socket

    Mysql-S mysql. sock file address

    For example,

    Mysql-S/tmp/mysql. sock

    Of course, as mentioned above, the following two login methods are connected to the mysql service by default through unix:

    Mysqlmysql-P 3307-u qunyingliu-p51cto201401_mysql-h localhost-P 3307-u qunyingliu-p51cto20140824

    If you connect to mysql through socket, when the mysql. sock file is not the default name or storage path, we will receive an error message similar to the following:

    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql. sock'

    2. Common Mysql interactive commands


  • Cancel authorization:
    Revoke all privileges (permission) on * (database). * (table) from account @ host;
    For example,

    Mysql> revove all privileges on *. * from qunyingliu @ "% ";

    Delete A User:
    Delete from user where user = "Account" and host = "% ";
    For example,

    Delete from user where user = "qunyingliu" and host = "% ";

    Database/data table/data column permissions:

    Alter: Modify existing data tables (such as adding/deleting columns) and indexes.

    Create: Create a new database or data table.

    Delete: Delete table records.

    Drop: delete a data table or database.

    INDEX: Create or delete an INDEX.

    Insert: Add Table records.

    Select: displays/searches for table records.

    Update: Modify existing records in the table.


    Global Management of MySQL user permissions:

    File: read and write files on the MySQL server.

    PROCESS: displays or kills service threads of other users.

    RELOAD: RELOAD Access Control tables and refresh logs.

    SHUTDOWN: Shut down the MySQL service.


    Special permissions:

    ALL: allow anything (same as root ).

    USAGE: Only logon is allowed. Other operations are not allowed.

  • View MySQL user permissions

  • View Current user (own) permission: show grants; view other MySQL user permissions: show grants for qunyingliu@10.2.122.1;
  • Mysql service operations

    View mysql version and current time:
    Select version (), current_date;
    Change Password:
    Update user set password = password ("liuqunying") where user = 'qunyingliu'; flush privileges (refresh permission)

  • 3. Mysql data backup and recovery
  • Back up the database:

  • Mysqldump-h host-u root-p database name -- default-character-set = utf8 [| gbk | latin1]> dbname_backup. SQL
  • Backup data table:

  • Mysqldump-h host-u root-p database name Table Name> dbname_tablename_backup. SQL

    Only SQL commands for data insertion can be exported:

    Mysqldump-h host-u root-p-t database name [Table name]> insert_data. SQL

    Export only the SQL command for creating a table:

    Mysqldump-h host-u root-p-d database name [Table name]> create_table. SQL

    Export only the data in the table:

    Mysqldump-h host-u root-p-T export data directory database table name
  • Recover Database: there are multiple methods to restore the database. We recommend that you run the source command to view the data import progress.
    Mysql + source command:

  • Qunyingliu_host # mysql-h host-u root-p mysql> use dbname; source dbname_backup. SQL;

    Mysqldump command:

    Qunyingliu_host # mysqldump-u username-p dbname <dbname_backup. SQL

    Mysql command:

    Login # mysql-u username-p-D dbname <mysql usage in the login environment qunyingliu_host # mysql-h host-uqunyingliu-login-e "SQL statement" qunyingliu_host # mysqladmin-h myhost -u root-p "SQL statement" 5. FAQs during mysql usage
  • Reset the logon password of the root account

    Qunyingliu_host #/etc/init. d/mysql stop qunyingliu_host # mysqld_safe -- user = mysql -- skip-grant-tables -- skip-networking & qunyingliu_host # mysql-u root mysql> update user set password = password ('qunyingliu @ 51cto ') where user = 'root'; mysql> flush privileges; mysql> quit qunyingliu_host #/etc/init. d/mysqld restart qunyingliu_host # mysql-uroot-p
  • To delete some data rows, use delete. Note that the where clause should be included. The rollback segment should be large enough.
    To delete a table, use drop
    Delete all data if you want to keep the table. If it is not related to the transaction, use truncate. If it is related to the transaction or you want to trigger the trigger, use delete.
    If you are sorting out the fragments in the table, you can use truncate to keep up with the reuse stroage, and then re-import/insert data/

    Practical application:
    Delete from table; // delete all data
    Truncate table; // The auto_increatement modulation starts from 0, actually starting from 1


    ##########################

    Reference:


    This article is from the "O & M personnel: From cainiao to laruence" blog. Be sure to keep this source

    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: info-contact@alibabacloud.com 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.