1 本地安装mysql 1.1 本地安装MySQL yum install mysql.x86_64 mysql-server.x86_64 1.2 启动mysql [root@wzlvm ~
1 本地安装mysql
1.1 本地安装MySQL
yum install mysql.x86_64 mysql-server.x86_64
1.2 启动mysql
[root@wzlvm ~]# /etc/init.d/mysqld start
1.3 创建数据库
1. 3.1 进入MySQL
[root@wzlvm ~]# mysqlWelcome to the MySQL monitor. Commands end with ; or /g.Your MySQL connection id is 2Server version: 5.1.73 Source distributionCopyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '/h' for help. Type '/c' to clear the current input statement.mysql> 1.3.2 创建数据库
mysql> show databases; # 查看默认有哪些数据库+--------------------+| Database |+--------------------+| information_schema || mysql || test |+--------------------+3 rows in set (0.00 sec)mysql> create database base1; # 创建数据库Query OK, 1 row affected (0.00 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || base1 | #=======》 刚刚创建的数据库| mysql || test |+--------------------+4 rows in set (0.00 sec)# 创建用户insert into mysql.user(Host,User,Password)values('localhost','user1','123456'); # 注意,在默认情况下mysql是不允许直接操作user表来创建用户，否则会报错 ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value 修改：去掉下面的STRICT_TRANS_TABLES [root@centos-export ~]# cat /etc/my.cnf|grep sql_mode sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 重启：service mysql restartcreate user 'ccssoft'@'localhost' identified by 'ccssoft@123'; # 授权 数据库base1 里面所有的表的所有权限都授权给用户user1从任意位置登录访问操作grant all on base1.* to user1@'%';# 上面的方法有点情况不能正常登陆，建议采用以下代码一次性创建用户和授权grant all on base1.* to user3@'%' identified by '123456';
1. 3.3 建表
先进入建好的数据库
mysql> use base1;Database changed建表
mysql> create table table1 (id int,remark varchar(200));Query OK, 0 rows affected (0.03 sec)插入数据
mysql> insert into table1(id,remark)values(1,'test1');Query OK, 1 row affected (0.00 sec)mysql> select * from table1;+------+--------+| id | remark |+------+--------+| 1 | test1 |+------+--------+1 row in set (0.00 sec)注意：有时候数据库表用户都建好列，但是就是链接不上，例如：
>>> db = pymysql.connect(host='127.0.0.1',user='wangzilong',password='123456',db='base1')Traceback (most recent call last): File "<input>", line 1, in <module> db = pymysql.connect(host='127.0.0.1',user='wangzilong',password='123456',db='base1') File "/data/program/python2.7/lib/python2.7/site-packages/pymysql/__init__.py", line 90, in Connect return Connection(*args, **kwargs) File "/data/program/python2.7/lib/python2.7/site-packages/pymysql/connections.py", line 688, in __init__ self.connect() File "/data/program/python2.7/lib/python2.7/site-packages/pymysql/connections.py", line 906, in connect self._request_authentication() File "/data/program/python2.7/lib/python2.7/site-packages/pymysql/connections.py", line 1114, in _request_authentication auth_packet = self._read_packet() File "/data/program/python2.7/lib/python2.7/site-packages/pymysql/connections.py", line 981, in _read_packet packet.check_error() File "/data/program/python2.7/lib/python2.7/site-packages/pymysql/connections.py", line 393, in check_error err.raise_mysql_exception(self._data) File "/data/program/python2.7/lib/python2.7/site-packages/pymysql/err.py", line 107, in raise_mysql_exception raise errorclass(errno, errval)OperationalError: (1045, u"Access denied for user 'wangzilong'@'localhost' (using password: YES)")可能是mysql.user 表中存在用户名为空定用户，删除空用户重启mysql因该就好了
注意：由于本地安装mysql一般版本都比较低很多功能都无法使用，所以不可取。下面介绍企业经常使用的二进制安装
2 二进制安装mysql
2.1 添加20G的磁盘
添加磁盘通过lvm添加，有利于以后磁盘空间扩展
添加详细步骤就不多介绍，之前已经有写过。
2.2 给mysql添加用户和组都是mysql
groupadd mysql
useradd -r -g mysql mysql
# mysql用户mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash# mysql 组mysql:x:27:2.3 挂载点路径下创建指定的目录，并把二进制安装包解压后的文件全部copy到product文件夹里面
[root@wzlvm mysql]# df -hFilesystem Size Used Avail Use% Mounted on/dev/sda3 18G 3.0G 14G 18% /tmpfs 495M 0 495M 0% /dev/shm/dev/sda1 1.9G 62M 1.8G 4% /boot/dev/sr0 3.7G 3.7G 0 100% /data/local_cdrom/dev/mapper/vgmysql-mysql 9.8G 1.2G 8.1G 13% /data/program/mysql # 刚刚新增的磁盘并创建了lvm 挂载在这个路径下载挂载点下面创建指定的文件夹：
mkdir -p /data/program/mysql/product
mkdir -p /data/program/mysql/data
mkdir -p /data/program/mysql/log
mkdir -p /data/program/mysql/backup
mkdir -p /data/program/mysql/tmp
mkdir -p /data/program/mysql/scripts
[root@wzlvm mysql]# lltotal 24drwxr-xr-x 2 mysql mysql 4096 Nov 7 22:24 backupdrwxr-xr-x 5 mysql mysql 4096 Nov 7 23:03 datadrwxr-xr-x 2 mysql mysql 4096 Nov 7 22:55 logdrwxr-xr-x 13 mysql mysql 4096 Nov 7 22:47 productdrwxr-xr-x 2 mysql mysql 4096 Nov 7 22:25 scriptsdrwxr-xr-x 2 mysql mysql 4096 Nov 7 22:55 tmp解压二进制安装包：
tar -zxvf mysql-5.6.20-linux-glibc2.5-x86_64.tar.gz
进入解压之后的目录下，全部移动到上面的product文件夹里面：
mv * /data/program/mysql/product/
给挂载点设置用户和组：
chown -R mysql:mysql /data/program/mysql
切换到mysql用户下：
su - mysql
进入目录：cd /data/program/mysql/product/scripts
设置mysql安装参数：
./mysql_install_db --user=mysql --datadir=/data/program/mysql/data --defaults-file=/data/program/mysql/product/my.cnf --basedir=/data/program/mysql/product/
注意：上面的路径要和我们手动创建的product路径一致
切换到root用户下：cp /data/program/mysql/product/support-files/mysql.server /etc/init.d/mysqld
配置mysql的启动文件：
vi /etc/init.d/mysqld
basedir=/data/program/mysql/product
datadir=/data/program/mysql/data
vim /data/program/mysql/product/my.cnf
[client]
port = 3369
socket = /data/program/mysql/tmp/mysql.sock
default-character-set=utf8
[mysqld]
port = 3369
server_id = 5
basedir = /data/program/mysql/product
datadir = /data/program/mysql/data
socket = /data/program/mysql/tmp/mysql.sock
pid_file=/data/program/mysql/tmp/mysql.pid
log_error = /data/program/mysql/log/error.log
innodb_buffer_pool_size = 8192M
innodb_file_per_table=1
tmp_table_size=64M
max_heap_table_size=64M
max_connections=600
character_set_server=utf8
autocommit=on
transaction_isolation=read-committed
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip-name-resolve=on
explicit_defaults_for_timestamp=on
lower_case_table_names=1
read_only=off
log_bin = /data/program/mysql/log/binlog
max_binlog_size=256m
binlog_format=row
expire_logs_days=30
innodb_log_file_size = 256m
innodb_log_files_in_group = 3
log_slave_updates=on
innodb_flush_log_at_trx_commit=0
log_bin_trust_function_creators=1
slow_query_log_file = /data/program/mysql/log/slow.log
slow_query_log=on
long_query_time=1
log_queries_not_using_indexes=on
/data/program/mysql/product/bin/mysqld_safe --defaults-file=/data/program/mysql/product/my.cnf &
mysql -uroot -p --socket=/data/program/mysql/tmp/mysql.sock
set password=password('123456');
flush privileges;
vi /etc/profile 加入以下信息
##database
export MYSQL_HOME=/data/program/mysql/product
export PATH=$PATH:/data/program/mysql/product/bin
注意：/etc/my.cnf 文件也要进行配置：
1 [mysqld] 2 datadir=/data/program/mysql/data 3 socket=/data/program/mysql/tmp/mysql.sock 4 user=mysql 5 # Disabling symbolic-links is recommended to prevent assorted security risks 6 symbolic-links=0 7 8 [mysqld_safe] 9 log-error=/data/program/mysql/log/error.log 10 pid-file=/data/program/mysql/tmp/mysql.pid启动mysql:
/etc/init.d/mysqld start
登录mysql：
mysqladmin -uroot password '123456' 修改密码
mysql -uroot -p 登录
查看数据库：
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+4 rows in set (0.00 sec)创建数据库，创建表，都在上面有说明，这里就不多说明了。
