安装MySQL

yum安装mysql

查看本机是否已经安装mysql
#rpm -qa | grep mysql
mysql-devel-5.1.73-8.el6_8.x86_64
mysql-libs-5.1.73-8.el6_8.x86_64
mysql-5.1.73-8.el6_8.x86_64

查看yum服务器上的mysql
#yum list |grep mysql

安装mysql服务器端
#yum install mysql-server
…
Installed:
mysql-server.x86_64 0:5.1.73-8.el6_8

Dependency Installed:
perl-DBD-MySQL.x86_64 0:4.013-3.el6 perl-DBI.x86_64 0:1.609-4.el6

Complete!
#rpm -qa |grep mysql
mysql-devel-5.1.73-8.el6_8.x86_64
mysql-server-5.1.73-8.el6_8.x86_64
mysql-libs-5.1.73-8.el6_8.x86_64
mysql-5.1.73-8.el6_8.x86_64

centos7:
#wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
#rpm -ivh mysql-community-release-el7-5.noarch.rpm
#ls -1 /etc/yum.repos.d/mysql-community*
/etc/yum.repos.d/mysql-community.repo
/etc/yum.repos.d/mysql-community-source.repo
#yum install mysql-server
mysql 初始化及相关配置

启动mysql服务

#service mysqld start
第一次启动mysql会初始化数据库

重启mysql
#service mysql restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]

给mysql的root账号设置密码

#mysqladmin -u root password ‘root’

操作数据库

登录mysql数据库

#mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.73 Source distribution

Copyright © 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>

查询数据库

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| test |
±-------------------+
3 rows in set (0.00 sec)

查询数据库中的表

mysql>use mysql;
mysql>show tables;

显示数据库表结构：

mysql>describe <表名>

建库和删库

mysql> create database 库名;
mysql> drop database 库名;

建表

mysql> use 库名;
mysql> create table 表名(字段列表);
例如：
mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

删表

mysql> drop table 表名;

往表中加入记录

mysql> insert into <表名> values (“linxiang”,“M”);

修改记录

mysql> update <表名> set sex = ‘m’ where name = ‘linxiang’;

删除表中的记录

delete from 表名 where 表达式

清空表中记录

delete from 表名;
truncate 表名

显示表中的记录

mysql> select * from 表名;

mysql配置文件

/etc/my.cnf

mysql数据库的数据库文件存放位置

/var/lib/mysql
创建一个数据库，来验证一下该数据库文件的存放位置
mysql> create database linxiang;
Query OK, 1 row affected (0.00 sec)
#ls -l /var/lib/mysql/
total 20492
-rw-rw----. 1 mysql mysql 10485760 Aug 2 10:53 ibdata1
-rw-rw----. 1 mysql mysql 5242880 Aug 2 10:53 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 Aug 2 10:51 ib_logfile1
drwx------. 2 mysql mysql 4096 Aug 3 02:07 linxiang
drwx------. 2 mysql mysql 4096 Aug 2 10:51 mysql
srwxrwxrwx. 1 mysql mysql 0 Aug 2 10:53 mysql.sock
drwx------. 2 mysql mysql 4096 Aug 2 10:51 test

日志位置

/var/log
mysqld.log

mysql的几个重要目录

数据库目录
/var/lib/mysql/
配置文件
/usr/share/mysql
（mysql.server命令及配置文件）
相关命令
/usr/bin
（mysqladmin mysqldump等命令）
启动脚本
/etc/rc.d/init.d/
（启动脚本文件mysql的目录）

端口号

#netstat -anp| grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 9120/mysqld

开启远程登录功能

mysql> GRANT ALL PRIVILEGES ON . TO ‘linx’@’%’ IDENTIFIED BY ‘linx’ WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

#mysql -h 10.109.100.1 -u linx -p
Enter password:

将数据导入表中：

mysql> LOAD DATA LOCAL INFILE “/home/tmp/user” INTO TABLE user;
Query OK, 5 rows affected, 5 warnings (0.00 sec)
Records: 5 Deleted: 0 Skipped: 0 Warnings: 5

导出数据库：

#mysqldump -u root -proot --databases linxiang > mysql.linxiang

导入数据库：

（数据库必须存在才能成功导入）
#mysql -u root -proot linxiang < mysql.linxiang

mysql客户端
MySQL-Front

忘记root用户密码：

2．修改MySQL的登录设置：
#vim /etc/my.cnf
在[mysqld]的段中加上一句：skip-grant-tables
例如：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables
保存并且退出vi。
3．重新启动mysqld
#service mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]
4．登录并修改MySQL的root密码
#mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 3.23.56
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> USE mysql ;
Database changed
mysql> UPDATE user SET Password = password ( ‘new-password’ ) WHERE User = ‘root’ ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0
mysql> flush privileges ;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
5．将MySQL的登录设置修改回来
#vim /etc/my.cnf
将刚才在[mysqld]的段中加上的skip-grant-tables删除
保存并且退出vim
6．重新启动mysqld
#service mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]

卸载yum安装的mysql

#rpm -qa |grep mysql
mysql-devel-5.1.73-8.el6_8.x86_64
mysql-libs-5.1.73-8.el6_8.x86_64
mysql-server-5.1.73-8.el6_8.x86_64
mysql-5.1.73-8.el6_8.x86_64

#yum remove mysql mysql-server mysql-libs mysql-deve

源码安装mysql

下载安装包，csdn
mysql-5.1.59.tar.gz
解压
#tar xzf mysql-5.1.59.tar.gz
#cd mysql-5.1.59
#./configure --prefix=/usr/local/mysql --without-debug --enable-thread-safe-client --with-pthread --enable-assembler --enable-profiling --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-extra-charsets=all --with-plugins=all --with-mysqld-user=mysql --without-embedded-server --with-server-suffix=-community --with-unix-socket-path=/tmp/mysql.sock
#make && make install
等待…10多分钟
创建mysql用户
#useradd mysql
设置mysql对mysql目录具有所有权限
setfacl -m u:mysql:rwx -R /usr/local/mysql
setfacl -m d:u:mysql:rwx -R /usr/local/mysql

配置mysql配置文件
#cp /usr/local/mysql/share/mysql/my-medium.cnf /etc/my.cnf
修改mysql配置文件
#vi /etc/my.cnf
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

安装mysql和test数据库
#/usr/local/mysql/bin/mysql_install_db --user=mysql

启动mysql数据库
/usr/local/mysql/bin/mysqld_safe --user=mysql &

设置开机启动
#vi /etc/rc.local
/usr/local/mysql/bin/mysqld_safe --user=mysql &

修改mysql登录密码
#/usr/local/mysql/bin/mysqladmin -uroot password 123
登录测试
/usr/local/mysql/bin/mysql -uroot -p123

停止mysql
mysqladmin -p -u root shutdown