After installing MySQL, configure the environment variables and then
Input MySQL
Go to MySQL
Then you can
Use MySQL;
Select * from user;
To view all users;
Execute the script file in MySQL:
Mysql> source XXX. SQL;
1.1 add users in two ways: (convert)
There are two different ways to add users: using the grant statement or directly operating the MySQL authorization table. The better way is to use the grant statement, because they are more concise and seem to have fewer errors.
The following example shows how to use a mysql client to install a new user. These examples assume that the permission has been installed by default. This means that, in order to change, you must be on the same machine that MySQL is running, you must be connected as the MySQL root user, and the root user must have the insert permission and reload management permission on the MySQL database. In addition, if you change the root user password, you must specify it using the following MySQL command.
You can add new users by issuing the grant statement:
Shell> MySQL -- user = root MySQL
Mysql> grant all privileges on *. * to Monty @ localhost
Identified by 'something' with grant option;
Mysql> grant all privileges on *. * to Monty @ "%"
Identified by 'something' with grant option;
Mysql> grant reload, process on *. * to admin @ localhost;
Mysql> grant usage on *. * to dummy @ localhost;
These grant statements install three new users:
MONTY: A full super user who can connect to the server from anywhere, but must use a password ('something' to do this. Note: You must issue a grant statement to Monty @ localhost and Monty @ "%. If we add a localhost entry, the entry created by mysql_install_db for the anonymous user entry of localhost takes priority when we connect from the local host, because it has a more specific host field value, therefore, the user table is arranged in the order of users.
Admin: a user who can connect from localhost without a password and is granted reload and process management permissions. This allows you to run the mysqladmin reload, mysqladmin refresh, mysqladmin flush-* commands, and mysqladmin processlist commands. No database-related permissions are granted. They can grant permissions in the future by issuing another grant statement.
Dummy: you can connect to a user without a password, but only from the local host. The global permission is set to 'n' -- the usage permission type allows you to set a user without permission. It assumes that you will grant database-related permissions in the future.
You can also directly add the same user access information by issuing an insert statement, and then tell the server to load the authorization table again:
Shell> MySQL -- user = root MySQL
Mysql> insert into user values ('localhost', 'monty ', password ('something '),
'Y ', 'y', 'y ')
Mysql> insert into user values ('%', 'monty ', password ('something '),
'Y ', 'y', 'y ')
Mysql> insert into user set host = 'localhost', user = 'admin ',
Reload_priv = 'y', process_priv = 'y ';
Mysql> insert into user (host, user, password)
Values ('localhost', 'dummy ','');
Mysql> flush privileges;
Depending on your MySQL version, for the above, you may have to use a different number of 'y' values (versions earlier than 3.22.11 have fewer permission columns ). For admin users, only the insert extension syntax that is more readable in version 3.22.11.
Note: To set a Super User, you only need to create a user table entry with the permission field set to 'y '. No dB or host table entries are required.
The permission columns in the User table are not explicitly set by the last insert statement (for dummy users), so those columns are assigned the default value 'n '. This is the same thing grant usage does.
In the following example, add a User custom, which can be connected from the host localhost, server. domain, and whitehouse.gov. He only wants to access the bankaccount database from localhost, the expenses database from whitehouse.gov, and the customer database from all three hosts. He wants to use the password stupid from all three hosts.
To use the grant statement to set permissions for individual users, run these commands:
Shell> MySQL -- user = root MySQL
Mysql> grant select, insert, update, delete, create, drop
On bankaccount .*
To custom @ localhost
Identified by 'stupid ';
Mysql> grant select, insert, update, delete, create, drop
On expenses .*
To custom@whitehouse.gov
Identified by 'stupid ';
Mysql> grant select, insert, update, delete, create, drop
On customer .*
To custom @ '%'
Identified by 'stupid ';
Run these commands by directly modifying the authorization table to set user permissions (Note: flush privileges at the end ):
Shell> MySQL -- user = root MySQL
Mysql> insert into user (host, user, password)
Values ('localhost', 'custom', password ('stupid '));
Mysql> insert into user (host, user, password)
Values ('server. Domain ', 'custom', password ('stupid '));
Mysql> insert into user (host, user, password)
Values ('whitehouse. gov ', 'custom', password ('stupid '));
Mysql> insert into DB
(Host, DB, user, select_priv, insert_priv, update_priv, delete_priv,
Create_priv, drop_priv)
Values
('Localhost', 'bankaccount', 'custom', 'y ');
Mysql> insert into DB
(Host, DB, user, select_priv, insert_priv, update_priv, delete_priv,
Create_priv, drop_priv)
Values
('Whitehouse. gov ', 'expenses', 'custom', 'y ');
Mysql> insert into DB
(Host, DB, user, select_priv, insert_priv, update_priv, delete_priv,
Create_priv, drop_priv)
Values ('%', 'customer', 'custom', 'y', 'y ');
Mysql> flush privileges;
The first three insert statements Add User table entries, allowing custom to connect from different hosts with a given password, but no permission is granted (all permissions are set to the default value 'n '). Add dB table entries in the last three insert statements, and grant custom database permissions to the bankaccount, expenses, and customer databases. However, the database can only be accessed from the correct host. Generally, when the authorization table is directly modified, the server must be notified to mount them again (use flush privileges) to make the permission modification take effect.
If you want to give a specific user access to any machine in a given domain, you can issue the following grant statement:
Mysql> Grant...
On *.*
To myusername @ "% .mydomainname.com"
Identified by 'mypassword ';
To do the same thing by directly modifying the authorization table:
Mysql> insert into user values ('% .mydomainname.com', 'myusername ',
Password ('mypassword '),...);
Mysql> flush privileges;
You can also use xmysqladmin, mysql_webadmin, or even xmysql to insert, change, and update values in the authorization table. You can find these functions in the contrib directory of MySQL. Program . (T004)
1.2 What should I do if I forget my password?
1. First kill the mysqld process:
Kill-term mysqld
2. Use the skip-grant-tables option to start MYSQL:
/Usr/bin/mysqld_safe-Skip-grant-tables
3. log on to change the password:
# Mysql-u root-P
Mysql> use MySQL;
Mysql> Update user SET Password = PASSWORD ('new _ pass') where user = 'root ';
Mysql> flush privileges;
Mysql> exit;
4. Turn off MySQL
# Mysqladmin-u root-P Shutdown
5. Start MySQL
/User/bin/mysqld_safe-user = MySQL &
2. How does MySQL Import and Export databases?
(Reproduced in http://hi.baidu.com/chenshengang/blog/item/644f7a16c49016064a90a7ab.html)
How to import and export a MySQL database
Release: | Author: Jianxin transparent | Source: local site Forum | view: 111 times
There are two ways to import a MySQL database:
1) Export the database SQL script first and then import it;
2) directly copy database directories and files.
In different operating systems or MySQL versions, the method of directly copying files may be incompatible.
Therefore, we recommend that you use SQL scripts to import data. The following describes two methods.
2. Method 1: SQL script
The procedure is as follows:
2.1. Export an SQL script
On the original database server, you can use the phpMyAdmin tool or the mysqldump command (the mysqldump command is located in the MySQL/bin/directory) command line to export the SQL script.
2.1.1 phpMyAdmin
Select "structure" and "data" to export. Do not add the "drop database" and "Drop table" options.
Select the "Save As file" option. If there is more data, select the "gzipped" option.
Save the exported SQL file.
2.1.2 use mysqldump command line
Command Format
Mysqldump-u username-P Database Name> database name. SQL
Example:
Mysqldump-uroot-p abc> ABC. SQL
(Export the database ABC to the ABC. SQL file)
Enter the password of the Database User Name When prompted.
2.2. Create an empty Database
Create a database on the master page/control panel. Assume that the database name is ABC and the database full user is abc_f.
2.3. Import and execute SQL scripts
You can use either phpMyAdmin or MySQL command line.
2.3.1 use phpMyAdmin
From the control panel, select the created empty database and click "manage" to go to the management tools page.
In the "SQL" menu, browse and select the exported SQL file, and click "run" to load and execute the file.
Note: phpMyAdmin has a limit on the size of the uploaded file, and PHP itself has a limit on the size of the uploaded file. If the original SQL File
Relatively large, you can use gzip to compress it. For text files such as SQL files, you can obtain a compression rate of or higher.
Gzip usage:
# Gzip XXXXX. SQL
Get
Xxxxx. SQL .gz file.
2.3.2 use MySQL Command Line
Command Format
Mysql-u username-P database name <database name. SQL
Example:
Mysql-uabc_f-p abc <ABC. SQL
(Import database ABC from ABC. SQL file)
Enter the password of the Database User Name When prompted.
3 method 2 Direct Copy
If the database is large, you can use the direct copy method. However, different versions and operating systems may be incompatible with each other, so use it with caution.
3.1 prepare the original file
Package A file with tar
3.2 Create an empty Database
3.3 decompress
Decompress the package in the temporary directory, for example:
CD/tmp
Tar zxf mydb.tar.gz
3.4 copy
Copy the extracted database file to the relevant directory.
CD mydb/
Cp */var/lib/MySQL/mydb/
For FreeBSD:
Cp */var/DB/MySQL/mydb/
3.5 permission settings
Change the owner of the copied files to MySQL: MySQL and the permission to 660.
Chown MYSQL: MySQL/var/lib/MySQL/mydb /*
Chmod 660/var/lib/MySQL/mydb /*
3. Use of Oracle in Linux
A really abnormal company. It's really troublesome to use ORACLE + MySQL for a project, but you still have to learn
Installing oracle in Linux is troublesome, but it is very important to check the environment in the early stage. You should install oracle as required.
Is used
Sqlplus/nolog
Connect xxx/xxx As sysdba;
Startup
./Dbca is a visual interface for creating databases.
Start/snrctl
./Netca -- start listener
Execute script files in Oracle
Sqlplus> @ xxx. SQL
