远程访问操作mysql数据库

1.使用yum命令安装mysql

    [root@bogon ~]# yum -y install mysql-server

2.设置开机启动

[root@bogon ~]#  chkconfig mysqld on

3.启动MySQL服务

[root@bogon ~]#  service mysqld start

4.设置MySQL的root用户设置密码

[root@bogon ~]#  mysql -u root

mysql> select user,host,password from mysql.user;  
+------+-----------+----------+  
| user | host      | password |  
+------+-----------+----------+  
| root | localhost |          |  
| root | bogon     |          |  
| root | 127.0.0.1 |          |  
|      | localhost |          |  
|      | bogon     |          |  
+------+-----------+----------+  
5 rows in set (0.01 sec)

5.查询用户的密码，都为空，用下面的命令设置root的密码为root

mysql> set password for root@localhost=password('root');

Query OK, 0 rows affected (0.00 sec)

6.创建mysql新用户test_user identified by'你的密码'

mysql> create user 'test_user'@'%' identified by 'test_user';

Query OK, 0 rows affected (0.00 sec)

7.给新用户test_user授权，让他可以从外部登陆和本地登陆

注意：@左边是用户名，右边是域名、IP和%，表示可以访问mysql的域名和IP，%表示外部任何地址都能访问。

mysql> grant all privileges on *.* to 'test_user'@'localhost' identified by 'test_user';  
Query OK, 0 rows affected (0.00 sec)  
mysql> grant all privileges on *.* to 'test_user'@'%' identified by 'test_user';  
Query OK, 0 rows affected (0.00 sec)  
mysql> select user,host,password from mysql.user;  
+----------+-----------+-------------------------------------------+  
| user     | host      | password                                  |  
+----------+-----------+-------------------------------------------+  
| root     | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |  
| root     | bogon     |                                           |  
| root     | 127.0.0.1 |                                           |  
|          | localhost |                                           |  
|          | bogon     |                                           |  
| test_user | %         | *3046CF87132BBD4FDDF06F321C6859074843B7D3 |  
| test_user | localhost | *3046CF87132BBD4FDDF06F321C6859074843B7D3 |  
+----------+-----------+-------------------------------------------+  
7 rows in set (0.00 sec)  
mysql> flush privileges;  
Query OK, 0 rows affected (0.01 sec)

8.查看mysql5.1的默认存储引擎

从下面的执行结果可以看出，mysql的默认引擎是MyISAM，这个引擎是不支持事务的。

mysql> show engines;  
+------------+---------+------------------------------------------------------------+--------------+------+------------+  
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |  
+------------+---------+------------------------------------------------------------+--------------+------+------------+  
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |  
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |  
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |  
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |  
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |  
+------------+---------+------------------------------------------------------------+--------------+------+------------+  
5 rows in set (0.00 sec)

9.修改mysql的默认引擎为InnoDB

9.1 停止mysql

mysql> exit;  
[root@bogon ~]# service mysqld stop

9.2 修改/etc/my.cnf

[mysqld] 后加入

default-storage-engine=InnoDB

加入后my.cnf的内容为:

[root@bogon etc]# more my.cnf  
[mysqld]  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock  
user=mysql  
# Disabling symbolic-links is recommended to prevent assorted security risks  
symbolic-links=0  
  
default-storage-engine=InnoDB  
  
[mysqld_safe]  
log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid

9.3 启动mysql

[root@bogon etc]# service mysqld start  
Starting mysqld:  [  OK  ]

9.4 查看mysql默认存储引擎

[root@bogon etc]# mysql -u root -p  
Enter password:   
Welcome to the MySQL monitor.  Commands end with ; or \g.  
Your MySQL connection id is 2  
Server version: 5.1.73 Source distribution  
  
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.  
  
Oracle is a registered trademark of Oracle Corporation and/or its  
affiliates. Other names may be trademarks of their respective  
owners.  
  
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  
  
mysql> show variables like 'storage_engine';  
+----------------+--------+  
| Variable_name  | Value  |  
+----------------+--------+  
| storage_engine | InnoDB |  
+----------------+--------+  
1 row in set (0.00 sec)

10.CentOS6.5开放mysql端口3306
CentOS6.5默认是不开放端口的，如果要让外部的系统访问CentOS6.5上的mysql，必须开放mysql的端口3306
10.1 修改/etc/sysconfig/iptables

添加下面一行

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

修改后iptables中的内容是

[root@bogon etc]# more /etc/sysconfig/iptables  
# Firewall configuration written by system-config-firewall  
# Manual customization of this file is not recommended.  
*filter  
:INPUT ACCEPT [0:0]  
:FORWARD ACCEPT [0:0]  
:OUTPUT ACCEPT [0:0]  
-A INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT  
-A INPUT -p icmp -j ACCEPT  
-A INPUT -i lo -j ACCEPT  
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT  
#添加配置项  
-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT  
-A INPUT -m state --state NEW -m tcp -p tcp --dport 11211 -j ACCEPT  
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT  
-A INPUT -j REJECT --reject-with icmp-host-prohibited  
-A FORWARD -j REJECT --reject-with icmp-host-prohibited  
  
COMMIT

11.重启防火墙

[root@bogon etc]# service iptables restart

远程拒绝访问的情况：

解决方法：实现远程连接(授权法)

mysql> use mysql;

Database changed

mysql> grant all privileges  on *.* to root@'%' identified by "root";

mysql> select host,user,password from user;

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

| host         | user | password                                  |

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

| localhost    | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |

| 192.168.1.1 | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |

| %            | root | *A731AEBFB621E354CD41BAF207D884A609E81F5E |

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

3 rows in set (0.00 sec)

这样机器就可以以用户名root密码root远程访问该机器上的MySql.

如果再遇到问题就看看你的服务器安全组有没有设，开放你的3306端口

2018/5/19 更新 修改root权限

mysql> use mysql

delete from user where user="root" and host!="localhost";

mysql> use mysql;  选择数据库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A


Database changed
mysql> update user set user="dns" where user="root";    将用户名为root的改为dns
Query OK, 4 rows affected (0.12 sec)
Rows matched: 4  Changed: 4  Warnings: 0


mysql> flush privileges;    刷新权限
Query OK, 0 rows affected (0.04 sec)


mysql> exit
Bye
root@local-Lenovo-G470:/home/local# 

改密码用mysqladmin -u 用户名 -p password 新密码

提示输入旧密码，输入即可。

mysql常用命令

查看数据库

mysql> show databases;

 

选择数据库

mysql> use bugfree;

 

设置字符集

mysql> set names 'gbk';

 

查询数据库中的表

mysql> show tables;

 

MySQL基本操作创建表

mysql> create table test(

 

-> tid int(10) not null,

 

-> tname varchar(100) not null,

 

-> tdate datetime not null default '0000-00-00',

 

-> primary key (tid));

 

查看表结构

mysql> desc test;

 

添加列

mysql> alter table test add(tage int(3));

 

修改原表结构

mysql> alter table test modify tage int(5) not null;

 

修改列的默认值

mysql> alter table test alter tage set default '0';

 

去掉列的默认值

mysql> alter table test alter tage drop default;

 

删除列

mysql> alter table test drop column tage;

 

插入数据

mysql> insert into test(tid,tname,tdate) value(1,'yangjuqi','2008-03-21');

 

查询数据

mysql> select * from test;

 

模糊查询

mysql> select * from test where tname like '%杨%';

 

修改数据

mysql> update test set tname='张三' where tid='2';

 

MySQL基本操作删除数据

mysql> delete from test where tid='2';

 

删除表

mysql>L> drop table test;

 

重命名表

mysql> alter table test rename testbak;

 

分页查询(limit 起始行，取多少行)

mysql> select * from testbak limit 2,1;

 

刷新数据库

mysql> flush privileges;

 

显示数据库版本

mysql> select version();

 

显示当前时间

mysql> select current_date;

 

将查询出的数据写入文件

mysql> select * from user into outfile "/etc/test.txt" fields terminated by ",";

 

查看数据库状态

mysql> status;

 

MySQL基本操作查看所有编码

mysql> show variables like 'character_set_%';

 

导入sql文件命令

mysql> source /etc/MySQL.sql;