How to create,import and export database on MySQL

by do son · Published · Updated

This article mainly describes the MySQL commonly used commands.

  • Create a database mysql -u root –p #Access to mysql service
    Enter password: # Enter the root password of the database.
    MySQL [(none)]> create database ddos; # Create an ddos database
    MySQL [(none)]> show databases; # View all databases
    MySQL [(none)]> drop database ddos; # Delete ddos database
    MySQL [(none)]> exit; ## Exit the database console
  • Create a database user
    add a user name for ddos, password 123456789, authorized for the local localhost exehack database all permissions

    # mysql -u root –p
    MySQL [(none)]> grant all privileges on ddos.* to ddos@’localhost’identified by ‘123456789’;
    MySQL [(none)]> flush privileges; # Permission to take effect immediately
    MySQL [(none)]> exit; # Exit the database console

  • Configure the MySQL remote connection
    For security reasons, ddos only allows the localhost to connect to the database, if you need to remote connect to the database, you need to make the following changes:MySQL [(none)]> grant all privileges on ddos.* to ddos@’%’ identified by ‘123456789’;

    Then open the iptables 3306 port

    # iptables -I INPUT 4 -p tcp -m state –state NEW -m tcp –dport 3306-j ACCEPT -j ACCEPT
    # service iptables save 

  • Modify the password of database

    mysql> set password for username@localhost = password(‘new_password’);

  • Export the entire databasemysqldump -u username -p –default-character-set=latin1 database_name > the_name_of_export_file
    mysqldump -u root -p wordpress > wordpress.sql

  • Export a tablemysqldump -u username -p Database_name Table_name> the_name_of_export_file
    mysqldump -u root -p wordpress users> wordpress_users.sql

  • Import the database
    Using sql command line#mysql -u root -p
    #mysql>use database
    #mysql>source wordpress_db.sql    Use the mysqldump command

    mysqldump -u username -p dbname < filename.sql

    Use the mysql command

    mysql -u username -p -D dbname < filename.sql

     

Tags: mysql