Fix Errors – Windows Errors Fix

1. Start and Stop MySQL Services

net stop mysql

net start mysql

2.Login into MySQL

MySQL- uusername – ppassword

Entering the command of MySQL-uroot-p, entering your password, then you can enter into in MySQL.

 

The prompt of MySQL is:

mysql >

Notice: if you are going to connect to another machine, you need to add a parameter -h machine IP

 

3. Add New Users

Format: grant privileges on database .* to username @ login host identified by “password”

For example, adding a user1 with password password 1 to login on the machine with query/ insert/ modify/ delete permission. First , within the root user connecting to MySQL, and then you enter the following command:

grant select, insert, update, delete on *.* to user1 @ localhost Identified by “password1″;

If you want the user to login onto mysql in any other machine , the localhost would be replaced by “%.”

If you do not want user1 owning his/her password, you can do it as follow

grant select, insert, update, delete on mydb .* to user1 @ localhost identified by “”;

 

4. Operat the Database

Log into mysql, and then you run the following commands at the mysql prompt, with each command ending with a semicolon.

4.1 Show the List of Databases.

show databases;

By default, there are two databases: MySQL and the Test. The information of MySQL system and its users’ rights would have been stored in database .Changing the password or adding users is saying the operating on this library.

4.2 Display the Data in the Table

use mysql;

show tables;

4.3 Show the Structure of the Data Table

describe table(name of the table);

4.4 Add or Delete Database in the Library

create database (database name);

drop database (database name);

4.5 Add Table:

use (library name);

create table (table name) ;

drop table (table name);

4.6 Clear Table Record

delete from table name;

4.7 Show the Table of Records

select * from (table name);

 

5. Move/Export/Import Data

5.1 Export Data

MySQLdump – opt test> MySQL.test

the database of Database test will be exported to MySQL.test file, which is a text file

E.g., MySQLdump-u root-p123456 – databases dbname> MySQL.dbname

database.Dbname is being Exported to the file of MySQL.dbname.

5.2 Import Data

MySQLimport-u root-p123456 <MySQL.dbname.

5.3 Import Text Data into the Database

The field data between the text data should be separated using Tab key.

use test;

load data local infile (filename) into table (table name);

5.3.1 Use the SHOW Statement to Find out All the Databases Currently Existing on the Server

MySQL> SHOW DATABASES;

5.3.2 Create a Database MYSQLDATA

MySQL> CREATE DATABASE MYSQLDATA;

5.3.3 Select the Database Which You Had Created

MySQL> USE MYSQLDATA; (Pressing Enter key, and it’d done when you met the instructions “Database changed” )

5.3.4 Check What Now Existing on the Database Table

MySQL> SHOW TABLES;

5.3.5 Create a Database Table

MySQL> CREATE TABLE MYTABLE [name VARCHAR (20), sex CHAR (1)];

5.3.6 Show the Table Structure

MySQL> DESCRIBE MYTABLE;

5.3.7 Add Records onto the Table

MySQL> insert into MYTABLE values (“hyq”, “M”);

5.3.8 Add onto the Database Table of Text Form (e.g., D: / mysql.txt)

MySQL> LOAD DATA LOCAL INFILE “D: / mysql.txt” INTO TABLE MYTABLE;

5.3.9 Import .Sql file (e.g., D: / mysql.sql)

MySQL> use database;

MySQL> source d: / mysql.sql;

5.3.10 Delete Table

MySQL> drop TABLE MYTABLE;

5.3.11 Empty Table

MySQL> delete from MYTABLE;

5.3.12 Update the Data in Table

MySQL> update MYTABLE set sex = “f” where name = ‘hyq’;

 

5.3.13 Back Up Database

 

mysqldump-u root (database name)> xxx.data14:

 

An example here:

 

Connecting to MYSQL on Remote Host

Assuming the IP of the Remote Host is: 110.110.110.110, user name is root, password is abcd123. Then you are going to type the following command:

 

mysql-h110.110.110.110-uroot-pabcd123

 

(Note: the space could not be added onto the u and the root; others may be done same)