Linux under the MySQL Oracle simple user manual _ database other

After installing MySQL, configure the environment variables, and then
Typing MySQL
Enter MySQL
Then you can
Use MySQL;
Select * from user;
To see all the users;
Execute script file under Mysql:
Mysql > Source xxx.sql;
1.1 Two ways to increase the user: (Turn)
There are 2 different ways to add users: by using the grant statement or by directly manipulating the MySQL authorization table. The better approach is to use the GRANT statement because they are more concise and seem to have fewer errors.
The following example shows how to install a new user using a MySQL client. These examples assume that permissions have been installed by default. This means that in order to change, you have to be in MySQL running on the same machine, you must be connected as a MySQL root user, and the root user must have Insert permissions and reload administrative privileges on the MySQL database. In addition, if you change the root password, you must specify it as the MySQL command below.
You can add a new user by issuing a GRANT statement:
shell> MySQL--user=root MySQL
Mysql> GRANT all privileges in *.* to Monty@localhost
Identified by ' something ' with GRANT OPTION;
Mysql> GRANT all privileges in *.* 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 3 new users:
Monty: A complete superuser who can connect to a server from anywhere, but must use a password (' Something ' to do this.) Note that we must issue a grant statement to Monty@localhost and monty@ "%". If we add a localhost entry, an entry created by mysql_install_db for the localhost anonymous user entry when we connect from the local host is preferred, because it has a more specific host field value, so it's an earlier arrival in the user table order.
Admin: Users who can connect from localhost without a password and are granted reload and process management privileges. This allows the user to perform mysqladmin reload, mysqladmin refresh and mysqladmin flush-* commands, as well as Mysqladmin processlist. No permissions are granted to the database. They can authorize it later by issuing another grant statement.
Dummy: A user can connect without a password, but only from the local host. Global permissions are set to ' N '--usage permission type allows you to set a user without permission. It assumes that you will grant database-related permissions at a later time.
You can also add the same user access information directly by issuing an INSERT statement, and then tell the server to reload the authorization table again:
shell> MySQL--user=root MySQL
mysql> INSERT into user VALUES (' localhost ', ' Monty ', PASSWORD (' something '),
' Y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y-axis ', ' Y '
mysql> INSERT into user VALUES ('% ', ' Monty ', PASSWORD (' something '),
' Y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y-axis ', ' 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 (there are fewer rights columns in previous versions of 3.22.11). For admin users, the syntax for only more readable insert extensions with versions that start with 3.22.11.
Note that in order to set up a superuser, you simply create a user table entry with the permission field set to ' Y '. No entries for DB or host tables are required.
The permission columns in the user table are not explicitly set by the last INSERT statement (to the dummy user), so those columns are given the default value ' N '. This is the same thing that grant usage did.
The following example adds a user custom, who can connect localhost, server.domain, and whitehouse.gov from the host. He only wants to access the BankAccount database from the localhost, access the expenses database from whitehouse.gov, and access the customer database from all 3 hosts. He wants to use the password stupid from all 3 hosts.
To use the GRANT statement to set permissions for a user, 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 ';
To run these commands by directly modifying the authorization table to set user permissions (note that 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 ', ' y ', ' y ', ' y ', ' y ', ' 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 ', ' y ', ' y ', ' y ', ' y ', ' 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 ', ' y ', ' y ', ' y ', ' y ');
mysql> FLUSH privileges;
The first 3 INSERT statements Add User table entries, allowing the user custom to connect from different hosts with a given password, but no license is granted (all permissions are set to the default value ' N '). The 3 INSERT statements Add DB table entries, grant custom permissions to the BankAccount, expenses, and customer database, but only when accessed from the correct host. Typically, when the authorization table is modified directly, the server must be told to mount them again (with flush privileges) in order for the permission modification to take effect.
If you want to give a specific user access to any machine on a given domain, you can issue a grant statement as follows:
Mysql> GRANT ...
On *.*
To myusername@ "%.mydomainname.com"
Identified by ' MyPassword ';
To do the same thing by directly modifying the authorization form, do this:
mysql> INSERT into user VALUES ('%.mydomainname.com ', ' myusername ',
PASSWORD (' MyPassword '),...);
mysql> FLUSH privileges;
You can also use Xmysqladmin, mysql_webadmin, and even xmysql to insert, change, and update values in the authorization table. You can find these utilities in the MySQL contrib directory. (T004)
1.2 Forget the password how to do?
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. Login Modify 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 Mysql imports the export database
(reprinted http://hi.baidu.com/chenshengang/blog/item/644f7a16c49016064a90a7ab.html)
How to import and export a MySQL database
Posted: 2008-4-01 11:26 | Author: Jian Xin | Source: Site Forum | View: 111 times
There are two ways to import a MySQL database:
1 lead out the database SQL script, and then import;
2 directly copy the database directory and files.
In the case of different operating systems or versions of MySQL, the method of directly copying files may be incompatible.
Therefore, it is generally recommended to import in SQL script form. Here are two ways to describe each.
2. Method one SQL script form
Action steps are as follows:
2.1. Export SQL Script
On the original database server, you can export the SQL script using the phpMyAdmin tool, or the mysqldump command line (mysqldump command in the mysql/bin/directory).
2.1.1 with phpMyAdmin Tools
Export options, select export structure and data, and do not add drop DATABASE and drop TABLE options.
Select the Save As File option to select the gzipped option if the data is more numerous.
Save the exported SQL file.
2.1.2 with mysqldump command line
Command format
Mysqldump-u user name-p database name > database name. sql
Example:
MYSQLDUMP-UROOT-P ABC > Abc.sql
(Export database ABC to abc.sql file)
When prompted for a password, enter the password for the database user name.
2.2. Create an empty database
Create a database from the Master interface/Control Panel. Suppose the database is named ABC and the database is Abc_f.
2.3. Import SQL script into execution
There are also two methods, one using the phpMyAdmin (MySQL database management) tool, or the MySQL command line.
2.3.1 with phpMyAdmin Tools
From the Control Panel, choose to create an empty database, click "Manage", and go to the admin tools page.
On the SQL menu, browse to select the SQL file you just exported and click "Execute" to download and execute.
Note: phpMyAdmin has limits on the size of uploaded files, and PHP itself has limits on the size of uploaded files, if the original SQL file
Relatively large, you can first use gzip to compress it, for SQL files such as text files, you can get 1:5 or higher compression rate.
Gzip Use method:
# gzip Xxxxx.sql
Get
xxxxx.sql.gz files.
2.3.2 with mysql command line
Command format
Mysql-u user name-p database name < database name. sql
Example:
MYSQL-UABC_F-P ABC < Abc.sql
(Import database ABC from Abc.sql file)
When prompted for a password, enter the password for the database user name.
3 Method Two direct copies
If the database is large, you can consider using a direct copy method, but the different versions and operating systems may be incompatible between the use of caution.
3.1 Preparation of original documents
Packaged in tar as a file
3.2 Creating an empty database
3.3 Decompression
Unzip in the temp directory, such as:
Cd/tmp
Tar zxf mydb.tar.gz
3.4 copies
Copy the extracted database files to the related directory
CD mydb/
CP */var/lib/mysql/mydb/
For FreeBSD:
CP */var/db/mysql/mydb/
3.5 Permission settings
Change the owner of the document copying the past to Mysql:mysql, with the permission changed to 660
Chown mysql:mysql/var/lib/mysql/mydb/*
chmod 660/var/lib/mysql/mydb/*
Oracle usage under 3.linux
Really sick company, a project to use Oracle+mysql, is really trouble, but still have to learn
Installing Oracle under Linux is a hassle, but the initial environmental check is important, or just follow the instructions to install it.
Use is
Sqlplus/nolog
Connect xxx/xxx as SYSDBA;
Startup
./DBCA is a visual interface for creating libraries.
Start/snrctl
./NETCA--Start listener
Execute script files under Oracle
sqlplus> @xxx. sql