准备环境：
5系统、MySQL 5.6 rpm包
首先 上传mysql至服务器
1、 解压缩MySql
[root@www.linuxidc.com]# 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@www.linuxidc.com]# 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@www.linuxidc.com]# rpm -e mysql-5.0.45-7.el5.i386 --nodeps
[root@www.linuxidc.com]# rpm -e mysql-server-5.0.45-7.el5.i386 –nodeps
[root@www.linuxidc.com]# rpm -ivh MySQL-server-5.6.2_m5-1.rhel5.i386.rpm
Preparing… ########################################### [100%]
1:MySQL-server ###########################################[100%]
[root@www.linuxidc.com]# rpm -ivh MySQL-client-5.6.2_m5-1.rhel5.i386.rpm
Preparing… ########################################### [100%]
1:MySQL-client ########################################### [100%]
3、 启动mysql并设置登录密码
[root@www.linuxidc.com]# /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.防火墙设置
当我们试图远程连接时，可能出现“Host XXX.XXX.XXX.XXX is not allowed to connect to this MySQL server”拒绝的连接的提示，这是没有设置防火墙的问题，增加3306端口的权限，实例如下：
vim /etc/sysconfig/iptables
# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended. *filter :INPUT ACCEPT [0:0] :FORWARD ACCEPT [0:0] :OUTPUT ACCEPT [0:0] :RH-Firewall-1-INPUT - [0:0] -A INPUT -j RH-Firewall-1-INPUT -A FORWARD -j RH-Firewall-1-INPUT -A RH-Firewall-1-INPUT -i lo -j ACCEPT -A RH-Firewall-1-INPUT -p icmp --icmp-type any -j ACCEPT -A RH-Firewall-1-INPUT -p 50 -j ACCEPT -A RH-Firewall-1-INPUT -p 51 -j ACCEPT -A RH-Firewall-1-INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT -A RH-Firewall-1-INPUT -p udp -m udp --dport 631 -j ACCEPT -A RH-Firewall-1-INPUT -p tcp -m tcp --dport 631 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 21 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 25 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 2049 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m udp -p udp --dport 137 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m udp -p udp --dport 138 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 139 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 445 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 443 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 23 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT -A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited COMMIT增加一行3306的权限，修改后如下：
# Firewall configuration written by system-config-securitylevel
# Manual customization of this file is not recommended. *filter :INPUT ACCEPT [0:0] :FORWARD ACCEPT [0:0] :OUTPUT ACCEPT [0:0] :RH-Firewall-1-INPUT - [0:0] -A INPUT -j RH-Firewall-1-INPUT -A FORWARD -j RH-Firewall-1-INPUT -A RH-Firewall-1-INPUT -i lo -j ACCEPT -A RH-Firewall-1-INPUT -p icmp --icmp-type any -j ACCEPT -A RH-Firewall-1-INPUT -p 50 -j ACCEPT -A RH-Firewall-1-INPUT -p 51 -j ACCEPT -A RH-Firewall-1-INPUT -p udp --dport 5353 -d 224.0.0.251 -j ACCEPT -A RH-Firewall-1-INPUT -p udp -m udp --dport 631 -j ACCEPT -A RH-Firewall-1-INPUT -p tcp -m tcp --dport 631 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state ESTABLISHED,RELATED -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 21 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 25 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 2049 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m udp -p udp --dport 137 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m udp -p udp --dport 138 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 139 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 445 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 443 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 23 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT -A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT -A RH-Firewall-1-INPUT -j REJECT --reject-with icmp-host-prohibited COMMIT保存退出
service iptables restart
重新启动，使生效
8、 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)
9、 增加mysql用户
格式：grantselect on 数据库.* to 用户名@登录主机 identified by "密码"
例1、增加一个用户user_1密码为123，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入MySQL，然后键入以下命令：mysql>grantselect,insert,update,delete on . to user1@"%" Identified by"123"; 例1增加的用户是十分危险的，如果知道了user1的密码，那么他就可以在网上的任何一台电脑上登录你的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";10、 备份与恢复
[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 恢复备份
绿色版mysql初始化:
mysqld --defaults-file=/opt/mysql/mysql/etc/my.cnf --initialize --user=rootmysqld --defaults-file=/opt/mysql/mysql/etc/my.cnf --initialize-insecure --user=root
以上命令前者初始化过程会给root用户设定一个随机密码的， 这个密码可以在mysql错误日志里找到， 后者不会设定密码， 我们可以用一下命令先进入mysql后给root用户设定自己的密码:
mysql -u root --skip-password
mysql8.0 修改账户密码:
use mysql;ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';FLUSH PRIVILEGES;