linux mysql create_mysql在linux下的建立

Mysql在Linux中的简单配置

1.启动Mysql服务命令:

[root@localhost ~]# service mysqld start

2.登录数据库,默认的用户名是root,没有密码. 输入mysql后,直接按Enter键操作数据库:

[root@localhost ~]# mysql

mysql>

3.设置root的密码:

[root@localhost ~]# mysqladmin -u root password "pwd"

4.使用密码登录数据库:

[root@localhost ~]# mysql -u root -ppwd

mysql>

5.设置数据库的编码格式为utf-8:

--查看mysql的默认编码:

mysql>show variables like 'character%'

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

| Variable_name | Value |

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

| character_set_client | latin1 |

| character_set_connection | latin1 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | latin1 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

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

--修改mysql的主要配置文件/etc/my.cnf文件. (如果/etc/目录下没有my.cnf文件,需要到/usr/share/mysql/下找到*.cnf文件,拷贝其中一个到/etc/并改名为my.cnf)中. 命令如下:

[root@localhost ~]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf 注:拷贝*.cnf文件到/etc/并改名为my.cnf

--编辑my.cnf文件,添加如下所示红色代码

[root@localhost ~]# vi /etc/my.cnf

[client]

default-character-set=utf8

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

lower_case_table_names=1

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

character-set-server=utf8

init_connect='SET NAMES utf8'

[mysql]

no-auto-rehash

default-character-set=utf8

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

--退出编辑命令:按Esc键,输入: 号,再输入x 保存并退出.这样数据库的编码格式就设定好了.

--重启mysql服务:

[root@localhost ~]# service mysqld restart

[root@localhost ~]# mysql -u root -ppwd

mysql>show variables like 'character%'

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

| Variable_name | Value |

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

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

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

6.创建用户:

mysql> insert into mysql.user(Host,User,Password) values ('localhost'，'name'，password('pwd'));

mysql> flush privileges; 注:刷新权限

7.创建数据库,库名为dbname:

mysql> create database dbname;

8.将操作数据库dbname的所有权限分配给name用户:

mysql> grant all privileges on dbname.* to name@'%' identified by 'pwd'; 注:这里的 '%' 可以用localhost代替,'%'的好处是远程机器可以用该用户访问

mysql> flush privileges;

mysql>\q 注:退出mysql

9.用root登录mysql,删除其他用户:

mysql> DELETE FROM user WHERE User='name' and Host='localhost';　　注:删除用户

mysql> drop database dbname;　　注:删除数据库

10.开放mysql的3306端口,使远程机器可以访问.

--首先确定要远程用户的host是否是%,如果不是请用root登录mysql并设置host为%

mysql> update mysql.user set host = '%' where user = 'name';

mysql> select host ,user from mysql.user;

--打开防火墙文件,增加如下红色行:

[root@localhost ~]# vi /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 3306 -j ACCEPT

-A INPUT -j REJECT --reject-with icmp-host-prohibited

-A FORWARD -j REJECT --reject-with icmp-host-prohibited

COMMIT

--重启防火墙:

[root@localhost ~]# service iptables restart

11.数据库中有表,可是查询总提示"表不存在". 一般情况是因为mysql在Linux下,表名对大小写是敏感的. 忽略大小写方法如下:

[root@localhost ~]# vi /etc/my.cnf

[client]

default-character-set=utf8

[mysqld]

lower_case_table_names=1　　注:添加此行代码. 等于0时:表示对大小写敏感; 等于1时:表示对大小不敏感;

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

character-set-server=utf8

init_connect='SET NAMES utf8'

[mysql]

no-auto-rehash

default-character-set=utf8

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

12 mysql在进行数据插入的时候提示

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

错误

在命令窗口

进行select @@sql_mode进行查看

之后进行修改

set@@sql_mode=NO_ENGINE_SUBSTITUTION