1.修改绑定ip地址
ubuntu18.04server上的mysql服务已经启动,本地可以连接,但其他机器不能通,telnet也不通:
查看发现绑定的ip位127.0.0.1,
所以得修改配置文件
刚开始在
vim /etc/mysql/f,没有发现bind-address选项,经查找要在下面文件修改(可能版本不同)
vim /etc/mysql/mysql.conf.d/f
重启服务:
/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;}