Linux环境下安装mysql
可以参考mysql网站文档来安装http://dev.mysql.com/doc/refman/5.6/en/installing.html
提供了各个操纵系统各种安装方式本人在RedHat下安装选择RPM包安装
1.下载资源http://repo.mysql.com/yum/mysql-5.6-community/el/5/i386/
下下来有包含下面这几个RPM
① mysql-community-client-5.6.19-2.el5.i386.rpm
② mysql-community-devel-5.6.19-2.el5.i386.rpm (这个包不安装会找不到mysql.h)
③ mysql-community-common-5.6.19-2.el5.i386.rpm
④ mysql-community-libs-5.6.19-2.el5.i386.rpm
⑤ mysql-community-server-5.6.19-2.el5.i386.rpm
2.root 登陆RedHat,useradd mysql 添加mysql 用户,passwd mysql 修改密码
3.卸载系统自带的Mysql
① Rpm -qa|grep mysql 或者rpm -qa|grep MySQL
② Rpm -e --nodeps 上面查询出来的rpm包名
4.安装
Rpm -ivh --nodeps --force mysql-community-client-5.6.19-2.el5.i386.rpm 依次安装上面5个rpm包
5.修改配置文件 vi /etc/my.cnf
默认mysql 数据库文件是在/var/lib/mysql目录下,如果要改变目录则修改/var/lib
我这边新增用户mysql就是用来存放mysql数据库文件 :%s#/var/lib#/home/mysql#g
文件内容如下:
[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=/home/mysql/mysql
socket=/home/mysql/mysql/mysql.sock
# 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
[client] 新增
socket=/home/mysql/mysql/mysql.sock
[mysqld_safe]
log-error=/home/mysql/mysqld.log
pid-file=/home/mysql/mysqld.pid
default-storage-engine=INNODB 新增
innodb_data_home_dir = /home/mysql/mysql
innodb_data_file_path = ibdata1:10M;autoextend
innodb_log_group_home_dir = /home/mysql/mysql
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
6.启动数据库 service mysqld start,如果启动失败查看/home/mysql/mysqld.log 失败日志
根据相应的错误信息来修改。楼主人品好,一次就成功了。
7.如果用的是默认路径/var/lib ,可以先执行mysql_install_db初始化配置数据库。楼主修改了默认路径,也执行了mysql_install_db 发现会在/var/lib下又生成相应的mysql文件夹,貌似要修改/usr/bin/mysql_config-32 这个文件里面写死了默认路径,有兴趣的可以试试看。
8.启动数据成功后,mysql -u root 登陆,use mysql ;, select host,user,password from user;
发现都不需要密码,删除其它用户,只留一个,并且设置密码
update user
set password=PASSWORD('123456')
where user='root';
新增用户
CREATE USER chat@'localhost' IDENTIFIED BY '123456’;
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost'WITH GRANT OPTION;
Flush privileges;刷新权限
新增数据库
Create database my_test;
Show databases;
9.新增用户test ,useradd -g mysql test;
Su - ;cd /home ; chmod 755 mysql ;使得同一组的test用户可以访问mysql用户下的mysql.sock 否则无法访问。
Mysql -u chat -p 登陆;输入密码成功登陆;
10.写一段c小程序测试下。对于mysql的c语言API可以查看下载下来的帮助文档,里面都包含。
#include <stdio.h>
#include <stdlib.h>
#include <mysql.h>
#define MYSQL_PORT 3306
int main(int argc, char *argv[])
{
char sName[3][20]={"wang 2","zhang san","li si"};
int i;
char execsql[1024];
MYSQL oMySql;
if(mysql_init(&oMySql) != NULL)
{
if(mysql_real_connect(&oMySql, "localhost", "chat", "123456", "my_test", MYSQL_PORT, NULL, 0) != NULL)
{
printf("Good connection!\n");
mysql_select_db(&oMySql, "chat_app");
memset(execsql,0x00,sizeof(execsql));
sprintf(execsql, "create table test(name char(10), age int(8))");
mysql_real_query(&oMySql, execsql, strlen(execsql));
mysql_select_db(&oMySql, "chat_app");
for(i=0; i<3; i++)
{
memset(execsql,0x00,sizeof(execsql));
sprintf(execsql,"insert into test values('%s', %d)", sName[i], 19+i);
printf("%s \n", execsql);
mysql_query(&oMySql, execsql);
}
mysql_commit(&oMySql);
}
else
{
printf("Connection Failed!\n");
}
}
else
{
printf("Initialization Failed.\n");
return -1;
}
mysql_close(&oMySql);
return 0;
}
gcc -g -o test test.c -I/usr/include/mysql test1.c -L/usr/lib/mysql -lmysqlclient
./test 发现无法连接数据库报Connection Failed
在Connection Failed前添加
printf(" errno[%d] errmsg[%s]\n",mysql_errno(&MySql),mysql_error(&MySql));
发现报
网上找了些资料据说配置 /etc/my.cnf 中添加 ,但是貌似还是报同样的问题,没纠结这个问题在环境变量中添加socket路径,使用mysql_real_connet时显示指定路径getenv(“client_socket”),重新编译,执行成功。现在就可以在linux环境下编写C代码了。
[client]
Socket=/home/mysql/mysql/mysql.sock .
11.mysql中文乱码问题
上面测试程序中字符如果将"zhang san"改为中文字符后,并执行程序后,会发现从数据库充查询显示的是乱码格式。
首先,我们要先确认数据库的字符集格式
mysql>show variables like "%char%"; 显示字符集格式
通过执行SET NAMES utf8的效果等同于同时设定如下:
SET character_set_client='utf8';
SET character_set_connection='utf8';
SET character_set_results='utf8';
--将相关参数全改为UTF8--
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
其次,创建表是指定字符集
create table test(name char(20) CHARSET utf8 COLLATE utf8_general_ci , age int(8)) ;
对于服务端字符集设置基本上都能支持中文了,对于客户端设置
编辑/etc/my.cnf文件添加配置
[mysql] default-character-set=utf8 我这边试了下好像没有用,于是在每次通过API函数来指定每次连接时的字符集格式。 (mysql_set_character_set(&mysql,"utf8") 然后再编译,执行,查询发现中文就能正常显示了。