1.修改绑定ip地址
ubuntu18.04server上的mysql服务已经启动,本地可以连接,但其他机器不能通,telnet也不通:
查看发现绑定的ip位127.0.0.1,
所以得修改配置文件
刚开始在
vim /etc/mysql/my.cnf,没有发现bind-address选项,经查找要在下面文件修改(可能版本不同)
vim /etc/mysql/mysql.conf.d/mysqld.cnf
重启服务:
/etc/init.d/mysql restart
2 .新建用户名及密码,开启远程连接权限
用户名:bo ,密码:123456
mysql> create user 'bo'@'%' identified by '123456';
mysql> grant all privileges on *.* to 'bo'@'%' identified by '123456' with grant option;
mysql> flush privileges;
3.C语言操作MySQL
#include "/usr/include/mysql/mysql.h"
#include <stdio.h>
#include <stdlib.h>
int main()
{
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
char server[] = "localhost";
char user[] = "bo";
char password[] = "123456";
char database[] = "mysql";
conn = mysql_init(NULL);
if (!mysql_real_connect(conn, server,user, password, database, 0, NULL, 0))
{
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}
if (mysql_query(conn, "show tables"))
{
fprintf(stderr, "%s\n", mysql_error(conn));
exit(1);
}
res = mysql_use_result(conn);
printf("MySQL Tables in mysql database:\n");
while ((row = mysql_fetch_row(res)) != NULL)
{
printf("%s \n", row[0]);
}
mysql_free_result(res);
mysql_close(conn);
printf("finish! \n");
return 0;
}
直接编译会出错:undefined reference to `mysql_init'
gcc -o test mysql_test.c `mysql_config --cflags --libs`
4.插入数据
先创建数据库和表:
CREATE DATABASE test;
CREATE TABLE IF NOT EXISTS `student`(
`id` INT UNSIGNED AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
#include "/usr/include/mysql/mysql.h"
#include <stdio.h>
#include <stdlib.h>
#include<time.h>
int main()
{
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
char *query;
int flag, t;
mysql_init(&mysql);
if(!mysql_real_connect(&mysql, "localhost", "root", "123456", "test", 0, NULL, 0)) {
printf("Failed to connect to Mysql!\n");
return 0;
}else {
printf("Connected to Mysql successfully!\n");
}
clock_t startTime = clock();
for(int i = 0; i < 100000; ++i)
{
char id_s[20] = {0};
char query_t[256] = {"insert into student(id,name) values("};
int id = i + 10;
sprintf(id_s,"%d",id);
strcat(query_t,id_s);
strcat(query_t,",'5')");
//query = "insert into student(id,name) values("+id+",'5')";
int ret = mysql_real_query(&mysql, query_t, (unsigned int)strlen(query_t));
if(ret)
{
printf("insert fail!\n");
return 0;
}
}
clock_t endTime = clock();
double totaltime = (double)(endTime - startTime) / CLOCKS_PER_SEC;
printf("total time:%fs\n",totaltime);
mysql_close(&mysql);
printf("finish! \n");
return 0;
}