准备环境:
Redhat 5系统、MySql5.6rpm包
首先 上传mysql至服务器
1、 解压缩MySql
[root@shiyue5u01]# tar -xvf MySQL-5.6.2_m5-1.rhel5.i386.tar
MySQL-client-5.6.2_m5-1.rhel5.i386.rpm
MySQL-devel-5.6.2_m5-1.rhel5.i386.rpm
MySQL-embedded-5.6.2_m5-1.rhel5.i386.rpm
MySQL-server-5.6.2_m5-1.rhel5.i386.rpm
MySQL-shared-5.6.2_m5-1.rhel5.i386.rpm
MySQL-test-5.6.2_m5-1.rhel5.i386.rpm
2、 安装MySQL-server包
[root@shiyue5u01]# rpm -ivh MySQL-server-5.6.2_m5-1.rhel5.i386.rpm
error:Failed dependencies:
MySQL conflicts with mysql-5.0.45-7.el5.i386
MySQL-serverconflicts with mysql-server-5.0.45-7.el5.i386
(安装失败,由于系统先前安装有mysql,把它卸载掉!再安装)
[root@shiyue5u01]# rpm -e mysql-5.0.45-7.el5.i386 --nodeps
[root@shiyue5u01]# rpm -e mysql-server-5.0.45-7.el5.i386 –nodeps
[root@shiyue5u01]# rpm -ivh MySQL-server-5.6.2_m5-1.rhel5.i386.rpm
Preparing... ########################################### [100%]
1:MySQL-server ###########################################[100%]
[root@shiyue5u01]# rpm -ivh MySQL-client-5.6.2_m5-1.rhel5.i386.rpm
Preparing... ########################################### [100%]
1:MySQL-client ########################################### [100%]
3、 启动mysql并设置登录密码
[root@shiyue5u01]# /etc/init.d/mysql start
StartingMySQL......[ OK ]
[root@shiyue5 u01]# mysqladmin -u rootpassword "123456"
4、 登录mysql并创建数据库
[root@shiyue5~]# mysql -u root -p
Enterpassword:
Welcometo the MySQL monitor. Commands end with; or \g.
YourMySQL connection id is 5
Serverversion: 5.6.2-m5 MySQL Community Server (GPL)
Copyright(c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracleis 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>create database shiyue;
QueryOK, 1 row affected (0.01 sec)
5、 配置客户端连接
mysql>use mysql;
Readingtable information for completion of table and column names
Youcan turn off this feature to get a quicker startup with -A
Database changed
mysql>grant all on *.* to root@"%" identified by "123456";
QueryOK, 0 rows affected (0.00 sec)
通过对1042问题代码的查询,得出编辑/etc/my.cnf文件,在[mysqld]内添加一行skip-name-resolve
在/etc/目录下如没有my.cnf配置文件,则请到/usr/share/mysql/下找到*.cnf文件,拷贝其中的一个到/etc/并改名为my.cnf
[root@shiyue5~]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
[root@shiyue5~]# vi /etc/my.cnf
#Example MySQL config file for medium systems.
#
# Thisis for a system with little memory (32M - 64M) where MySQL plays
# animportant part, or systems up to 128M where MySQL is used together with
#other programs (such as a web server)
#
#MySQL programs look for option files in a set of
#locations which depend on the deployment platform.
# Youcan copy this option file to one of those
#locations. For information about these locations, see:
#http://dev.mysql.com/doc/mysql/en/option-files.html
#
# Inthis file, you can use all long options that a program supports.
# Ifyou want to know which options a program supports, run the program
# withthe "--help" option.
# Thefollowing options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Herefollows entries for some specific programs
# TheMySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size= 16M
max_allowed_packet= 1M
table_open_cache= 64
sort_buffer_size= 512K
net_buffer_length= 8K
read_buffer_size= 256K
read_rnd_buffer_size= 512K
myisam_sort_buffer_size= 8M
skip-name-resolve
#Don't listen on a TCP/IP port at all. This can be a security enhancement,
# ifall processes that need to connect to mysqld run on the same host.
# Allinteraction with mysqld must be made via Unix sockets or named pipes.
"/etc/my.cnf"145L, 4700C written
6、 重启mysql
[root@shiyue5~]# /etc/init.d/mysql restart
Shuttingdown MySQL...[ OK ]
Starting MySQL...[ OK ]
7、 mysql数据库的日常维护管理
mysql>show databases;(显示数据库)
+--------------------+
|Database |
+--------------------+
|information_schema |
|mysql |
|performance_schema |
|shiyue |
|test |
+--------------------+
5 rows in set (0.00 sec)
mysql>create database ddimcn;(创建数据库)
QueryOK, 1 row affected (0.01 sec)
mysql>
mysql>use ddimcn (打开库)
Databasechanged
mysql>show tables; (显示表)
Emptyset (0.00 sec)
mysql>create table test (id int(10),name varchar(10)); (创建表)
QueryOK, 0 rows affected (0.14 sec)
mysql>show tables; (显示表)
+------------------+
|Tables_in_ddimcn |
+------------------+
|test |
+------------------+
1row in set (0.01 sec)
mysql>describe test; (显示表结构)
+-------+-------------+------+-----+---------+-------+
|Field | Type | Null | Key | Default| Extra |
+-------+-------------+------+-----+---------+-------+
|id | int(10) | YES | | NULL | |
|name | varchar(10) | YES | |NULL | |
+-------+-------------+------+-----+---------+-------+
2rows in set (0.17 sec)
mysql> insert into test values(1,'zhangsan'); 插入数据
QueryOK, 1 row affected (0.07 sec)
mysql>insert into test values (2,'lisi');
QueryOK, 1 row affected (0.03 sec)
mysql>insert into test values (3,'wangwu');
QueryOK, 1 row affected (0.00 sec)
mysql>select * from test; 查询表中数据
+------+----------+
|id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+------+----------+
3rows in set (0.00 sec)
mysql>update test set name='york' where id=1; 更新数据
QueryOK, 1 row affected (0.07 sec)
Rowsmatched: 1 Changed: 1 Warnings: 0
mysql>select * from test;
+------+--------+
|id | name |
+------+--------+
| 1 | york |
| 2 | lisi |
| 3 | wangwu |
+------+--------+
3rows in set (0.00 sec)
8、 增加mysql用户
格式:grantselect on 数据库.* to 用户名@登录主机 identified by "密码"
例1、增加一个用户user_1密码为123,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MySQL,然后键入以下命令:
mysql>grantselect,insert,update,delete on *.* to user_1@"%" Identified by"123";
例1增加的用户是十分危险的,如果知道了user_1的密码,那么他就可以在网上的任何一台电脑上登录你的MySQL数据库并对你的数据为所欲为了,解决办法见例2。
例2、增加一个用户user_2密码为123,让此用户只可以在localhost上登录,并可以对数据库aaa进行查询、插入、修改、删除的操作(localhost指本地主机,即MySQL数据库所在的那台主机),这样用户即使用知道user_2的密码,他也无法从网上直接访问数据库,只能通过 MYSQL主机来操作aaa库。
mysql>grant select,insert,update,delete on aaa.* to user_2@localhostidentified by "123";
9、 备份与恢复
[root@shiyue5 ~]# cd/var/lib/mysql/
[root@shiyue5 mysql]# ll
total 28776
-rw-rw---- 1 mysqlmysql 56 Aug 12 23:08 auto.cnf
drwx------ 2 mysqlmysql 4096 Aug 12 23:41 ddimcn
-rw-rw---- 1 mysql mysql18874368 Aug 12 23:47 ibdata1
-rw-rw---- 1 mysqlmysql 5242880 Aug 12 23:47 ib_logfile0
-rw-rw---- 1 mysqlmysql 5242880 Aug 12 23:08 ib_logfile1
drwx--x--x 2 mysqlmysql 4096 Aug 12 22:52 mysql
-rw-rw---- 1 mysqlmysql 1107 Aug 12 23:47mysql-bin.000001
-rw-rw---- 1 mysqlmysql 19 Aug 12 23:34mysql-bin.index
srwxrwxrwx 1 mysqlmysql 0 Aug 12 23:34 mysql.sock
drwx------ 2 mysqlmysql 4096 Aug 12 22:52performance_schema
-rw-r--r-- 1 root root 115 Aug 12 22:52 RPM_UPGRADE_HISTORY
-rw-r--r-- 1 mysqlmysql 115 Aug 12 22:52RPM_UPGRADE_MARKER-LAST
drwx------ 2 mysqlmysql 4096 Aug 12 23:11 shiyue
-rw-rw---- 1 mysqlroot 9318 Aug 12 23:34shiyue5.oracle.com.err
-rw-rw---- 1 mysqlmysql 5 Aug 12 23:34 shiyue5.oracle.com.pid
drwxr-xr-x 2 mysqlmysql 4096 Aug 12 22:52 test
[root@shiyue5 mysql]#mysqldump -p --opt ddimcn > back_ddimcn (备份)
Enter password:
[root@shiyue5 mysql]# mysql -u root -p ccc < back__ddimcn 恢复备份