centos7下安裝mysql及測試centos

念念爸 2016-06-17

展開全文

步驟1：下載并安裝MySQL

# wget http://dev./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 內(nèi)容如下：

# For advice on how to change settings please see
# http://dev./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服務(wù)，配置文件才能生效

# service mysqld restart

步驟4：連接mysql服務(wù)

# mysql -uroot -p

按空格健即可，因為初次安裝mysql，root賬號并沒有設(shè)置密碼。

設(shè)置密碼的方法：

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

例如：這里，設(shè)置root賬號的登陸密碼為 123456

步驟5：簡單操作mysql

1）連接mysql數(shù)據(jù)庫服務(wù)

2）創(chuàng)建數(shù)據(jù)庫db_test，創(chuàng)建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;

強(qiáng)調(diào)，ENGINE=InnoDB DEFAULT CHARSET=utf8; 保證了中文插入數(shù)據(jù)庫出現(xiàn)亂碼的情況！

可以通過如下命令查看編碼格式：

show variables like "%char%";

這里之所以保證了編碼格式均為utf8，源于配置文件/etc/my.cnf 的設(shè)置

#----------------- UTF-8 -----------------#
skip-character-set-client-handshake
init-connect='SET NAMES utf8'
character-set-server=utf8

character_set_database=utf8
#----------------- UTF-8 -----------------#

及創(chuàng)建表的時候聲明了

ENGINE=InnoDB DEFAULT CHARSET=utf8;

3）簡單插入一條數(shù)據(jù)，并查看

步驟6：在nodejs中操作mysql數(shù)據(jù)庫

1）遠(yuǎn)程授權(quán)連接mysql

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

mysql> flush privileges;

格式：grant all on 數(shù)據(jù)庫名字 .* to 用戶名@localhost identified by ‘密碼’

刷新權(quán)限。flush privileges

2）新建mysql.js 代碼如下：

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

var pool = mysql.createPool({    //創(chuàng)建一個線程池
  connectionLimit: 10,          //允許同時有10個線程訪問mysql
  host: 'localhost',            //本地IP
  user: 'yzx',                  //用戶名 yzx
  password: '123456',           //密碼 123456
  database: 'db_test'           //數(shù)據(jù)庫名字
});

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); 
});

運行結(jié)果如下：

3）插入一條數(shù)據(jù) 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);
});