1. Added the login format after password as follows:
Mysql -u root -p
2, modify the login password
1) mysqladmin -u username -P old password Password new password
Example: MySQLADMIN -U Root Password 21century
Note: Because the root does not have a password, the -p old password can be omitted. Or mysql -u root -pmysql> set password for root = password ("456"); mysql -u root -pmysql -u root -p mysql> set password for root = password ("456"); if you use root to enter It can be abbreviated: set password = password ("456");
2) Modify the root user password of the USER table directly:
mysql> user mysql;
MySQL> Update User Set Pasword = Password ('21century') where user = 'root';
Mysql> Flush Privileges; / * Force refreshing the memory authorization form, otherwise or buffer password * /
Note: Password ('21century') is encrypted with MD5 to encrypt the password 21century. At this time, the password seen in the table is a long string letter, which increases security.
3, whether the test password is modified successfully
1) Log in without password
[root @ Test1 local] # mysql
Error 1045: Access Denied for User: 'root @ localhost' (Using Password: NO)
Display errors, the password has been modified.
2) Log in with the modified password
[root @ Test1 local] # mysql -u root -p
ENTER Password: (Enter the modified password 21century)
Welcome to the mysql monitor. Commands end with; or / g.
Your MySQL Connection ID IS 177 TO Server Version: 3.23.48
Type 'help;' or '/ h' for help. Type '/ c' to clear the buffer.
MySQL>
success!
This is to modify the password through the mysqladmin command, or change the password by modifying the library.
4, start and stop
Start: MySQL starts from version 3.23.15, and the default installation After the service is started with mysql users, the root user is not allowed to start.
If you need to start with the root user, you must add -user = root parameters
(./safe_mysqld --user = root &) 4 start-up mode optional: 1) Service mysql start 2) /etc/rc.d/init.d/mysql start 3) mysqld_safe 4) SAFE_MYSQLD
Stop: 3 Relax Option 1: 1) Service MySQL Stop 2) /etc/rc.d/init.d/mysql stop 3) mysqladmin -u root -P Shutdown
5. Export and import Meeting Database: Export: 1) Example 1. Back up all the tables in the database Meeting to Meeting_200409.SQL text file mysqldump -uroot -p --opt meeting> Meeting_200409.sql 2) Example 2. Only back up Database the part of the table school teacher and student mysqldump -uroot -p --opt school teacher student> school_teacher_student_200409.sql 3) Example 3. backup multiple databases mysqldump --databases school test> school_test_200409.sql data recovery:
1) Example 1. Database School is inadvertently destroyed, restore data from School_200409.SQL backup files Mysql> Create Database School; Mysql> Use School; MySQL> Source School_200409.SQL; # Restore 2) Example 2. Restore Table Teacher and Student MySQL> Use School; MySQL> Source School_Teacher_Student_200409.SQL; # Restore 3) Example 3. Restore multiple database mysql> Source School_test_200409.sql; # Restore
Example 1. Database School accidentally destroyed, recover data from SCHOOL_2004_9.SQL backup file
mysql
Mysql> Create Database School;
Mysql> Use school;
MySQL> Source School_2004_9.sql; # Restore
Example 2. Recovery Table Teacher and Student
mysql
Mysql> Use school;
mysql> Source School_Teacher_Student_2004_9.sql; # Restore
Example 3. Restore multiple databases
mysql
MySQL> Source School_test_2004_9.sql; # Restore
Example 1. Database School accidentally destroyed, recover data from SCHOOL_2004_9.SQL backup file
mysql
Mysql> Create Database School;
Mysql> Use school;
MySQL> Source School_2004_9.sql; # Restore
Example 2. Recovery Table Teacher and Student
mysql
Mysql> Use school;
mysql> Source School_Teacher_Student_2004_9.sql; # Restore
Example 3. Restore multiple databases
mysql
MySQL> Source School_test_2004_9.sql; # Restore
Import: 1) Import DBNAME Database
MySQLDUMP -UROOT -P21Century DBNAME 2) Importing a database can also be used in a way that is similar to ORACLE @ my_script.sql once a large number of SQL statements, which is very useful when using mysqldump. Example: #. / Mysql -uroot -p
(Note: CREATE DATABASE, Use DatabaseName, CREATE TABLE, and INSERT INTO statements can be written in the top files) 3) Use the data incoming command. MySQL> Load Data Infile '/TMP/teacher.txt' /TMP/teacher.txt 'Into Table Teacher; (Note: Data in Teacher.txt must use Tab key separation, table is the database name, Teacher is a table name) 4) Use mysqlimport mysqlimport school / TMP /TEacher.txt (Note: The above command indicates importing the data in Teacher.txt into the Teacher table in this database. School indicates the name of the database name, the name of the text file is the name of the base table to be inserted. This file is used to use the Tab key separation.) 6, rename the table Rename Table ZTemp to Ztemp4; 7, modify field properties Alter Table Bbabase Change News_id ID VARCHAR (5) Not NULL; 8, add a field after Content in the table ALTER TABLE BBABASE ADD Leave_time DateTime Not Null After Content; ALTER TABLE T2 DROP Column Col2; 9, increasing users Grant SELECT, UPDATE, INSERT ON Database Name. Table Name To Username @ Login Host Identified by "Password" Example 1: Grant SELECT, UPDATE, INSERT, DELETE ON *. * To Test @ dev identified by "abc"; Example 2: Grant SELECT, UPDATE, INSERT, DELETE ON *. * To Test @% Identified by "abc"; / *% indicates that the host, localhost indicates a local host * / Example 3: Create a local complete superuser admin , Password is '123' grant all privileges on *. * To admin @ localhost identified by '123' 10. Just use the MySQL service using the mysql service. You can also add -skip-networking parameters when starting to make MySQL misselect any TCP / IP connection (./safe_mysqld --skip-networking ", increasing security. (Very recommended) 11. What should I do if I forget the root password? Plus parameters - Skip-grant-tables when starting the MySQL server to skip the authentication of the license table (./SAFE_MYSQLD --SKIP-GRANT-TABLES &) So we can log in directly to the MySQL server, then modify the password of the root user, restart MySQL can log in with a new password. 12. Date in the data type of MySQL represents a recent field, which is no time second, if you want to bring the day-to-date second, you need to use the DateTime type.
