mysql for centos_centos7下安装mysql及测试centos

步骤1： 下载并安装MySQL

# wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm

# rpm -ivh mysql-community-release-el7-5.noarch.rpm

# yum install mysql-community-server

步骤2：修改配置文件 /etc/my.cnf 内容如下：

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

#----------------- UTF-8 -----------------#

skip-character-set-client-handshake

init-connect='SET NAMES utf8'

character-set-server=utf8

character_set_database=utf8

#----------------- UTF-8 -----------------#

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Recommended in standard MySQL setup

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

步骤3：重启mysql服务，配置文件才能生效

# service mysqld restart

步骤4：连接mysql服务

# mysql -uroot -p

按空格健即可，因为初次安装mysql，root账号并没有设置密码。

设置密码的方法：

mysql> set password for ‘root’@‘localhost’ = password('123456');

例如：这里，设置root账号的登陆密码为 123456

步骤5：简单操作mysql

1)连接mysql数据库服务

2)创建数据库db_test，创建user表

mysql> create database db_test;

mysql> use db_test;

mysql> create table user(  -> id INT NOT NULL AUTO_INCREMENT,  -> name varchar(25) ,  -> gender varchar(10),  -> mobile varchar(40),  -> Create_time date,  -> primary key(id)  -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;

强调，ENGINE=InnoDB DEFAULT CHARSET=utf8; 保证了中文插入数据库出现乱码的情况！

可以通过如下命令查看编码格式：

show variables like "%char%";

这里之所以保证了编码格式均为utf8，源于配置文件/etc/my.cnf 的设置

#----------------- UTF-8 -----------------#

skip-character-set-client-handshake

init-connect='SET NAMES utf8'

character-set-server=utf8

character_set_database=utf8

#----------------- UTF-8 -----------------#

及创建表的时候声明了

ENGINE=InnoDB DEFAULT CHARSET=utf8;

3)简单插入一条数据，并查看

步骤6：在nodejs中操作mysql数据库

1)远程授权连接mysql

mysql> grant all on db_test .* to yzx@localhost identified by '123456';

mysql> flush privileges;

格式：grant all on 数据库名字 .* to 用户名@localhost identified by ‘密码’

刷新权限。flush privileges

2)新建mysql.js 代码如下：

var mysql = require('mysql'); //引入mysql包，(npm install mysql --save)

var pool = mysql.createPool({ //创建一个线程池

connectionLimit: 10, //允许同时有10个线程访问mysql

host: 'localhost', //本地IP

user: 'yzx', //用户名 yzx

password: '123456', //密码 123456

database: 'db_test' //数据库名字

});

pool.getConnection(function (err, connection) { //开启连接

if (err) throw err;

var value = 'yzx';

var query = connection.query('SELECT * FROM user WHERE name=?', value, function (err, result) {

if (err) throw err;

console.log(result);

connection.release();

});

console.log(query.sql);

});

运行结果如下：

3)插入一条数据 mysql.js代码修改如下：

var mysql = require('mysql');

var pool = mysql.createPool({

connectionLimit: 10,

host: 'localhost',

user: 'yzx',

password: '123456',

database: 'db_test'

});

pool.getConnection(function (err, connection) {

if (err) throw err;

var sql='INSERT INTO user (name,gender,mobile) values(?,?,?)';

var sqlParams = ['小叶', '男', '10086'];

var query = connection.query(sql, sqlParams, function (err, result) {

if (err) throw err;

console.log("result: ",result);

connection.release();

});

console.log("query.sql: ",query.sql);

});

在本地查看数据库