Linux下MySQL开放root的远程访问权限 & 其他一些常用操作记录

(1)Linux下MySQL开放root的远程访问权限

环境:RHEL 5.3

安装了MySQL之后,MySQL的root用户默认是不开放远程访问权限的。为了开放它,只需要两步:

用Linux系统的root用户登录系统,然后:

开放3306端口的远程连接权限:

在“-A RH-Firewall-1-INPUT -j REJECT –reject-with icmp-host-prohibited”这一行的上面(注意,一定是上面),添加如下一行:

-A RH-Firewall-1-INPUT -m state –state NEW -m tcp -p tcp –dport 3306 -j ACCEPT

然后重启iptables:

service iptables restart

如果你要仅向指定的服务器开放,你需要类似于这样添加iptables规则:

-A RH-Firewall-1-INPUT -s 192.168.17.1 -p tcp -m tcp --dport 3306 -j ACCEPT

-A RH-Firewall-1-INPUT -s 192.168.17.2 -p tcp -m tcp --dport 3306 -j ACCEPT

-A INPUT -p tcp -m tcp --dport 3306 -j REJECT

这表示只有192.168.17.1和192.168.17.2两台服务器可以访问本机的MySQL。如果在非授权的服务器上telnet本服务器的3306端口,则会看到下面的错误提示:

telnet: connect to address XXX.XXX.XXX.XXX: Connection refused

telnet: Unable to connect to remote host: Connection refused

文章来源:http://www.codelast.com/

为MySQL的root用户添加远程访问权限:
先修改文件
/etc/mysql/my.cnf,把里面的“bind-address = 127.0.0.1”这一句注释掉,然后:

[root@localhost ~]# mysql -uroot -p
Enter password: 
(此处输入密码登录)
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35 to server version: 5.0.22

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> grant all on *.* to root@’%’ identified by ‘your_password’;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye

[root@localhost ~]# /etc/init.d/mysql restart

搞定。

 

(2)在一台Linux主机上连接远程MySQL服务器的命令:

mysql -h 192.168.XXX.XXX -u root --port=12015 -p

然后会弹出输入密码的提示(“Enter password:”),输入密码即可登录到MySQL命令行。其中,192.168.XXX.XXX是远程MySQL主机的IP地址,12015是MySQL服务端口,-u root表示以root用户登录。

 

(3)在MySQL命令行下获取一个表的所有字段构成

用命令:

SHOW COLUMNS FROM 表名;

会输出形如下面的结果:

+-----------+--------------+------+-----+---------+----------------+

| Field     | Type         | Null | Key | Default | Extra          |

+-----------+--------------+------+-----+---------+----------------+

| ID        | bigint(20)   | NO   | PRI | NULL    | auto_increment | 

| num   | int(6)      | YES  |     | NULL    |                | 

+-----------+--------------+------+-----+---------+----------------+

2 rows in set (0.01 sec)

 

(4)如何写这样的查询条件:两个字段的时间差大于/小于/等于某一个值

两个datetime类型的字段A和B(其值形如“2011-01-20 10:22:30”),如何在SQL查询的WHERE语句中,加上类似于“A字段与B字段的时间差在3天之内”的条件?

你可以用DATEDIFF函数来实现:

WHERE DATEDIFF(A, B) < 3

但是,A和B这两个日期时间,谁大还不一定,所以,在计算它们的差值的时候,我可以再加一个绝对值函数:

WHERE ABS(DATEDIFF(A, B)) < 3

这样就做到了。不过请注意,这样比较出来的结果,是不包含时间的,只比较日期的差值,大家可以自己试验一下。

文章来源:http://www.codelast.com/

(5)查看MySQL中有哪些SQL语句正在执行

SHOW FULL PROCESSLIST;

即可看到正在执行的进程信息,输出形如:

+----+-------------+-------------------+--------+---------+------+-------+-----------------------+

| Id | User        | Host              | db     | Command | Time | State | Info                  |

+----+-------------+-------------------+--------+---------+------+-------+-----------------------+

|  7 | root | 192.168.1.111:45695   | student_db | Query   |    269 | NULL  | select id from student | 

+----+-------------+-------------------+--------+---------+------+-------+-----------------------+

其中第一列是进程的ID,要中止某一个进程,执行命令 kill ID号 即可。

文章来源:http://www.codelast.com/

(6)如何从一个数据库中,以最简单的方式复制几个表(包括表结构和数据)到另一个数据库中?

如果你有系统的管理员权限,可以操作MySQL数据库目录下的数据文件,那么直接拷贝文件是最简单的方式:

假设你要将A数据库里的表“stu”(包括表结构和表数据)复制到数据库B中,B数据库里原来没有stu表,现在你可以在找到A数据库存放数据文件的目录,拷贝出 stu.frm,stu.MYD,stu.MYI 三个文件拷贝到B数据库相应目录下,MySQL都不用重启,刷新一下table的列表就可以看到stu表了,非常方便。

 

 

(7)查看MySQL版本号的SQL命令

SELECT VERSION();


(8)用shell读取MySQL的记录
这里假设一种最简单的情况:要获取my_db数据库中,表 my_table 的字段 my_field 的值为5的记录数。

REC_COUNT=`mysql -h 192.168.XXX.XXX -u root -pMyPassword -P 3306 --database=my_db -Bse "SELECT COUNT(*) FROM my_table WHERE my_field = 5"
echo $REC_COUNT

文章来源:http://www.codelast.com/
(9)如何查询一张表的所有字段名

SELECT column_name FROM information_schema.columns WHERE table_schema = 'db_name' AND table_name = 'table_name';

其中,“db_name”是数据库名,“table_name”是表名。

(10)创建一个与已经存在的表一样结构的表
假设已存在表 table_a,则以下操作:

CREATE TABLE table_b LIKE table_a;

将创建一个表 table_b,并且其结构与 table_a 一样。
如果要在创建表的时候连数据一起复制,可以这样操作:

CREATE TABLE table_b SELECT * FROM table_a;

(11)查询数据库中是否有指定的表名

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME LIKE '%abc%';

其中,db_name 是数据库名,%abc% 表示查询表名中含有abc的那些表。

(12)如打开一个数据表时,提示“Out of resources when opening file ...”的解决办法
这里假设你的MySQL Server是在Ubuntu下安装的(其他的Linux发行版应该也类似),那么,遇到这个问题时,应该是你的MySQL和系统的默认配置不符合需要导致的。为了验证这一点,进入MySQL的命令行,执行如下命令:

mysql> show variables like 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024 |
+------------------+-------+

这个1024就是默认值,并且太小了,应该修改成65535。修改方法如下:
在 /etc/mysql/my.cnf 中,[mysql] 这个配置段里添加一行:

open-files-limit = 65535

这一行在默认的配置中应该是不存在的,默认值就是1024,我们显式地修改为65535。然后重启MySQL Server:

/etc/init.d/mysql restart

文章来源:http://www.codelast.com/
另外还有一些文章说,就算你修改了my.cnf中的这个值,也不一定有用,因为它是取系统ulimit -n的值和open-files-limit之间的最小值,所以你需要同时修改系统文件 /etc/security/limits.conf,添加如下两行并且重启服务器:

* soft nofile 65535
* hard nofile 65535

我不确定是否一定要这样做。我只修改了MySQL配置并重启了MySQL Server就解决了问题。

(13)将MySQL中某个表里的符合指定条件的记录dump出来(保存为SQL语句)

mysqldump -u root -h 192.168.1.26 db_name table_name --where "ID>123" > output.sql

然后就会弹出让你输密码的提示,输完后回车即可执行。
其中,root表示使用root用户登录MySQL,192.168.1.26是数据库的地址,db_name是数据库名,table_name是表名,--where 表示只有符合其后的条件的记录才会被导出,后面的双引号里就是指定的条件,在这里,我的table_name里的主键是ID,因此它表示只有ID>123的记录会被导出。
最后记录被保存到了output.sql这个文件中。
但是,请千万当心:导出的sql文件中,是包含“DROP TABLE IF EXISTS table_name”这样的语句的,因此,如果你拿这个导出的sql文件来导入另一个数据库,它会导致你的另一个数据库的对应表先被清空(再导入数据)!所以,如果这不是你想要的结果,请手工修改导出的sql文件,不要造成严重后果

文章来源:http://www.codelast.com/
所以,这里既然已经说到了导出sql文件,那么就应该提一下将它导入另一个数据库的方法。最傻的方法就是打开此sql文件,复制里面的SQL语句,然后在任意的MySQL client软件中执行这些SQL语句。但是这样执行速度很慢,我们可以先用命令行的MySQL client登录另一台服务器:

mysql -u root -h 192.168.1.27 -p

输入密码登录,然后执行:

mysql> \. /home/codelast/output.sql

即可将前面导出的sql文件导入数据库。其中,“\.”表示执行一个sql文件,/home/codelast/output.sql就是刚才导出的sql文件的完整路径。

(14)查找某个字段里含有下划线("_")的记录
看似简单,但是这样是不行的(想要查询出my_table表里,my_field字段(该字段类型为text)里含有下划线的记录):

SELECT my_field, FROM my_table WHERE my_field LIKE '%_%';

因为在这里,下划线表示“一个字符”,所以要对它转义才行,所以应该这样做:

SELECT my_field, FROM my_table WHERE my_field LIKE '%/_%' ESCAPE '/';

然后就没问题了。这表示对"/"后面的字符进行转义。

发表评论

电子邮件地址不会被公开。 必填项已用*标注