Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "mysql and oracle simple manual under Linux". In the actual case operation process, many people will encounter such difficulties. Next, let Xiaobian lead you to learn how to deal with these situations! I hope you can read carefully and learn something!

After installing mysql, configure the environment variables and

Type in mysql

go to the MySQL

may then

Use mysql;

Select * from user;

to view all users;

Execute script files under Mysql:

Mysql > source xxx.sql;

1.1 There are two ways to add users: (turn)

There are two different ways to add users: by using the GRANT statement or by directly manipulating the MySQL authorization table. A better approach is to use GRANT statements because they are more concise and seem to make fewer errors.

The following example shows how to install new users using MySQL clients. These examples assume that permissions are installed by default. This means that in order to change, you must be on the same machine MySQL is running on, you must be connected as MySQL root, and root must have insert and reload administrative privileges on the MySQL database. Also, if you change the root password, you must specify it with the MySQL command below.

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 superuser who can connect to the server from anywhere, but must use a password ('something 'to do this. Note that we must issue GRANT statements for monty@localhost and monty@"%." If we add the localhost entry, the anonymous user entry for localhost will take precedence over the entry created by mysql_install_db when we connect from the localhost, because it has a more specific Host field value, so it comes earlier in the user table order.

admin: A user who can connect from localhost without a password and is granted reload and process administrative privileges. This allows users to execute mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin processlist. No database permissions granted. They can authorize it later by issuing another GRANT statement.

Dummy: A user who 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 later.

You can also add the same user access information directly by issuing an INSERT statement and telling the server to reload the authorization table:

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','Y')

mysql> INSERT INTO user VALUES('%','monty',PASSWORD('something'),

'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','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, you may have to use a different number of 'Y' values for the above (versions prior to 3.22.11 had fewer permission columns). For admin users, use only the more readable INSERT extension syntax that versions starting with 3.22.11 have.

Note that in order to set up a superuser, you only need to create a user table entry with the permissions 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 what GRANT USAGE does.

The following example adds a user custom that can connect from 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 3 hosts.

To set permissions for a user using the GRANT statement, 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 privileges (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 three INSERT statements add user table entries that allow user custom to connect from different hosts with the given password, but grant no permissions (all permissions are set to default 'N'). The last three INSERT statements add db table entries, granting custom the bankaccount, expenses, and customer database privileges, but only if accessed from the correct host. Usually, when authorization tables are modified directly, the server must be told to reload them (with FLUSH PRIVILEGES) in order for the permission modifications to take effect.

If you want to give a particular user access from any machine in a given domain, you can issue a GRANT statement like this:

mysql> GRANT ...

ON *.*

TO myusername@"%.mydomainname.com"

IDENTIFIED BY 'mypassword';

To do the same by modifying the authorization table directly, do this:

mysql> INSERT INTO user VALUES ('%.mydomainname.com', 'myusername',

PASSWORD('mypassword'),...);

mysql> FLUSH PRIVILEGES;

You can also insert, change, and update values in the authorization table using xmysqladmin, mysql_webadmin, or even xmysql. You can find these utilities in MySQL's Contrib directory. (T004)

1.2 What if I forget my password?

1. Kill the mysqld process first:

Kill -TERM mysqld

2. Start MySQL with skip-grant-tables:

/usr/bin/mysqld_safe -skip-grant-tables

3. Login Change 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 the MySQL

/user/bin/mysqld_safe -user=mysql&

2. Mysql How to import and export databases

(Reposted from http://hi.baidu.com/chenshengang/blog/item/644f7a16c49016064a90a7ab.html)

How to import and export MySQL databases

Published: 2008-4-01 11:26| Author: Sword Heart Tongming| Source: Home Forum| See also: 111

MySQL database import, there are two ways:

1)Export database SQL script first, then import;

2)Copy database directories and files directly.

Incompatibility may occur between methods of copying files directly under different operating systems or MySQL versions.

Therefore, it is generally recommended to import SQL scripts. Two methods are described below.

2. Method 1 SQL Script Form

The procedure is as follows:

2.1. Export SQL Script

From the original database server, SQL scripts can be exported using the phpMyAdmin tool or the mysqldump command line (mysqldump command is located in the mysql/bin/directory).

2.1.1 Using phpMyAdmin Tool

In Export Options, select Export Structure and Data and do not add Drop DATABASE and Drop TABLE options.

Check the "Save as file" option, if the data is more, you can check the "gzipped" option.

Save the exported SQL file.

2.1.2 Using the mysqldump command line

command format

mysqldump -u username-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 username.

2.2. Create an empty database

Create a database through the main control interface/control panel. Assume that the database name is abc and the database full user is abc_f.

2.3. Import SQL scripts for execution

Again, there are two ways to do this, either with the phpMyAdmin (mysql database management) tool or from the mysql command line.

2.3.1 Using phpMyAdmin Tool

From the control panel, select the empty database created, click "Manage" to enter the management tools page.

In the SQL menu, browse to select the SQL file you just exported, click Execute to upload and execute.

Note: phpMyAdmin has a limit on the size of uploaded files, php itself has a limit on the size of uploaded files, if the original sql file

It is relatively large and can be compressed with gzip first. For text files such as sql files, compression ratios of 1:5 or higher can be obtained.

How to use gzip:

# gzip xxxxx.sql

get

xxxxx.sql.gz file.

2.3.2 Using mysql command line

command format

mysql -u username-p database name

< 数据库名.sql 范例： mysql -uabc_f -p abc < abc.sql （导入数据库abc从abc.sql文件） 提示输入密码时，输入该数据库用户名的密码。 3 方法二 直接拷贝 如果数据库比较大，可以考虑用直接拷贝的方法，但不同版本和操作系统之间可能不兼容，要慎用。 3.1 准备原始文件 用tar打包为一个文件 3.2 创建空数据库 3.3 解压 在临时目录中解压，如： cd /tmp tar zxf mydb.tar.gz 3.4 拷贝 将解压后的数据库文件拷贝到相关目录 cd mydb/ cp * /var/lib/mysql/mydb/ 对于FreeBSD: cp * /var/db/mysql/mydb/ 3.5 权限设置 将拷贝过去的文件的属主改为mysql:mysql，权限改为660 chown mysql:mysql /var/lib/mysql/mydb/* chmod 660 /var/lib/mysql/mydb/* 3.linux 下的oracle使用 真是变态的公司，一个项目还要用oracle+mysql，真是麻烦，不过还得学 Linux下安装oracle比较麻烦，但是初期的环境检查很重要，还是老老实实的按照要求安装吧。 使用就是 Sqlplus /nolog Connect xxx/xxx as sysdba; Startup ./dbca是个创建库的可视化界面。 启动 /snrctl ./netca --启动listener Oracle下执行脚本文件 Sqlplus>

@xxx.sql

"Linux mysql and oracle simple manual" content is introduced here, thank you for reading. If you want to know more about industry-related knowledge, you can pay attention to the website. Xiaobian will output more high-quality practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.