目录
检查当前的虚拟机中是否已经安装MySQL了:
[root@hadoop102 etc]# rpm -qal | grep mysql-libs
/usr/share/doc/mysql-libs-5.1.73
/usr/share/doc/mysql-libs-5.1.73/COPYING
/usr/share/doc/mysql-libs-5.1.73/README.mysql-license
卸载MySQL,
[root@hadoop102 etc]# rpm -e --nodeps mysql-libs
[root@hadoop102 etc]# rpm -qal | grep mysql-libs
[root@hadoop102 etc]#
[root@hadoop102 etc]#
给 / tmp 较大权限
检查一下 / tmp 目录的权限,因为mysql在安装的过程中,会通过mysql用户在 / tmp 目录下新建tmp db文件,所以要给/tmp较大的权限
[root@hadoop102 ~]# cd /tmp
[root@hadoop102 tmp]# ll
总用量 16
drwx------. 2 root root 4096 10月 16 19:50 keyring-jsMGXD
drwx------. 2 gdm gdm 4096 10月 19 09:18 orbit-gdm
drwx------. 2 root root 4096 10月 18 19:09 pulse-c7vM0RwEtIFT
drwx------. 2 gdm gdm 4096 10月 19 09:18 pulse-QdcS2h6Va3in
-rw-------. 1 root root 0 10月 16 22:06 yum.log
[root@hadoop102 tmp]# chmod -R 777 /tmp
上传rpm包到 opt 目录,rpm安装mysql
然后使用文件上传工具,将实现准备好的mysql的rpm,上传到/opt 目录下,cd到该目录,执行如下的安装命令:
[root@hadoop102 tmp]# cd /opt
[root@hadoop102 opt]# ll
总用量 54040
-rw-r--r--. 1 root root 15329084 10月 19 14:26 MySQL-client-5.5.52-1.el6.x86_64.rpm
-rw-r--r--. 1 root root 40000200 10月 19 14:26 MySQL-server-5.5.52-1.el6.x86_64.rpm
drwxr-xr-x. 2 root root 4096 3月 26 2015 rh
[root@hadoop102 opt]# rpm -ivh MySQL-client-5.5.52-1.el6.x86_64.rpm
warning: MySQL-client-5.5.52-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-client ########################################### [100%]
[root@hadoop102 opt]# rpm -ivh MySQL-server-5.5.52-1.el6.x86_64.rpm
warning: MySQL-server-5.5.52-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing... ########################################### [100%]
1:MySQL-server ########################################### [100%]
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h hadoop102 password 'new-password'
省略,,,
查看当前安装的mysql的版本信息和用户和用户组信息:
[root@hadoop102 opt]# mysqladmin --version
mysqladmin Ver 8.42 Distrib 5.5.52, for Linux on x86_64
查看MySQL的用户和用户组:
[root@hadoop102 opt]# cat /etc/passwd
**
sshd:x:74:74:Privilege-separated SSH:/var/empty/sshd:/sbin/nologin
tcpdump:x:72:72::/:/sbin/nologin
apache:x:48:48:Apache:/var/www:/sbin/nologin
mysql:x:496:493:MySQL server:/var/lib/mysql:/bin/bash
[root@hadoop102 opt]# cat /etc/group
root:x:0:
bin:x:1:bin,daemon
**
tcpdump:x:72:
apache:x:48:
mysql:x:493:
启动mysql服务,并给root设置密码:
[root@hadoop102 ~]# service mysql start
Starting MySQL.. [确定]
[root@hadoop102 ~]# /usr/bin/mysqladmin -u root password '123'
登陆mysql并查看mysql的安装目录:
[root@hadoop102 ~]# mysql -uroot -p123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.52 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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>
查看MySQL的安装目录:
[root@hadoop103 /]# ps -ef | grep mysql
root 3091 1 0 Oct19 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/hadoop103.pid
mysql 3185 3091 0 Oct19 pts/0 00:00:01 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/hadoop103.err --pid-file=/var/lib/mysql/hadoop103.pid
root 3255 2335 0 00:18 pts/0 00:00:00 grep mysql
文件相关的作用表:
--basedir /usr/bin 相关命令目录 mysqladmin mysqldump等命令
--datadir /var/lib/mysql/ mysql数据库文件的存放路径
--plugin-dir /usr/lib64/mysql/plugin mysql插件存放路径
--log-error /var/lib/mysql/jack.atguigu.err mysql错误日志路径
--pid-file /var/lib/mysql/jack.atguigu.pid 进程pid文件
--socket /var/lib/mysql/mysql.sock 本地连接时用的unix套接字文件
/usr/share/mysql 配置文件目录 mysql脚本及配置文件传递
/etc/init.d/mysql 服务启停相关脚本
或者使用whereis命令:
[root@hadoop103 /]# whereis mysql
mysql: /usr/bin/mysql /usr/lib64/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz
设置MySQL开机自启动:
[root@hadoop102 ~]# chkconfig --level 5 mysql on
[root@hadoop102 ~]# chkconfig --list | grep mysql
mysql 0:关闭 1:关闭 2:启用 3:启用 4:启用 5:启用 6:关闭
或者是使用ntsysv,用窗口来选择。
现在连接数据库,创建数据库,并创建一张user表如下:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)
mysql> create table user(id int,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| user |
+------------------+
1 row in set (0.00 sec)
mysql> insert into user values(1,'张飞');
Query OK, 1 row affected, 1 warning (0.01 sec)
查询刚刚插入的数据:
mysql> select * from user;
+------+------+
| id | name |
+------+------+
| 1 | ?? |
+------+------+
1 row in set (0.00 sec)
查看字符集:
mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
可以看出客户端使用的是utf8,而服务器端使用的是拉丁文。两者的自字符集不匹配。现在修改字符集
首先修改数据库的字符集,然后在添加数据测试:
mysql> alter database testdb character set 'utf8';
Query OK, 1 row affected (0.00 sec)
mysql> insert into user values(2,'关羽');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> select * from user;
+------+------+
| id | name |
+------+------+
| 1 | ?? |
| 2 | ?? |
+------+------+
2 rows in set (0.00 sec)
发现,对于已经添加的数据,是无法更改回来的,新添加的数据依然会出现乱码,接着我们查看创建表的时候使用的是什么字符集,
mysql> show create table user;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=Latin |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
修改表的字符集:
mysql> alter table user convert to character set 'utf8';
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into user values(3,'刘备');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user;
+------+--------+
| id | name |
+------+--------+
| 1 | ?? |
| 2 | ?? |
| 3 | 刘备 |
+------+--------+
3 rows in set (0.00 sec)
这样,只是配置了testdb的字符集,现在永久的将mysql设置为所有utf8的字符集。
配置mysql的字符集为utf8
[root@hadoop102 mysql]# cp my-huge.cnf /etc/my.cnf
[root@hadoop102 mysql]# cd /etc
[root@hadoop102 etc]# vim my.cnf
修改的部分如下:
default-character-set = utf8
[mysqld]
character_set_server = utf8
character_set_client = utf8
collation-server = utf8_general_ci
这个配置文件在执行了mysql -uroot -p的时候,mysql是一个可执行的脚本文件,在脚本文件中会读取配置文件 [ my.cnf ]的代码。然后在重新启动mysql。mysql这个脚本的目录在
/etc/init.d目录下。
设置MySQL大小写不敏感
Linux系统大小写敏感,我们在在l使用Linux连接上数据库然后进项相关的查询等操作的时候,大小写的内容是不一样的,如何解决这个问题?
[root@hadoop102 etc]# vim my.cnf
lower_case_table_names = 1
[root@hadoop102 etc]# service mysql restart
设置sql_mode为敏感
在开发和生产模式下,都需要将该值设置为严格模式,如果不设置会导致什么样子的结果呢?
select max(age),name deptid form stu group by deptid;
类似以上的select语句,在带有group by的查询语句中,select子句的内容必须是聚合函数或者是group by子句中的字段。但是mysql是无法检测出来上面的这样的查询语句的,如果要检查,我么必须通过以下的方式进行设置:在配置文件 my.cnf 中的mysqld字段添加 :
sql_mode=ONLY_FULL_GROUP_BY
保存退出并重新启动,就会生效。