How to create,import and export database on MySQL

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.sqlUse the mysqldump command

  mysqldump -u username -p dbname < filename.sql
  

  Use the mysql command

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