MySQL（mysql怎么读）

25-04-14 4

针对MySQL和mysql怎么读这两个问题，本篇文章进行了详细的解答，同时本文还将给你拓展AccessMySQLfromC（用C访问MySQL数据库）、Can''tconnecttolocalMySQ

针对MySQL和mysql怎么读这两个问题，本篇文章进行了详细的解答，同时本文还将给你拓展Access MySQL from C （用 C 访问 MySQL 数据库）、Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)、Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''、CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’等相关知识，希望可以帮助到你。

本文目录一览：

MySQL（mysql怎么读）
Access MySQL from C （用 C 访问 MySQL 数据库）
Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)
Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''
CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’

MySQL（mysql怎么读）

MySQL

MySQL 连接状态

查看所有连接状态

show processlist;

输出结果：

+----+------+-----------------+-------+---------+------+----------+------------------+
| Id | User | Host            | db    | Command | Time | State    | Info             |
+----+------+-----------------+-------+---------+------+----------+------------------+
|  9 | root | localhost:49386 | NULL  | Sleep   |   37 |          | NULL             |
| 10 | root | localhost:49391 | test  | Sleep   |   22 |          | NULL             |
| 11 | root | localhost:49396 | test  | Sleep   |   31 |          | NULL             |
| 12 | root | localhost:49401 | sonar | Sleep   |   28 |          | NULL             |
| 13 | root | localhost:49414 | test  | Sleep   |   21 |          | NULL             |
| 14 | root | localhost:49432 | test  | Query   |    0 | starting | show processlist |
+----+------+-----------------+-------+---------+------+----------+------------------+

断开连接
```
kill 12; -- 12为连接的id
```
连接线程的 command
- Sleep 等待刻划断的查询
- Query 正在执行查询
- Locked 等待表锁的释放
- Sorting result 正在对结果排序
- Sending data 正在发挥数据

MySQL 的查询过程

mysql 的架构图：
mysql 的查询流程图
mysql 的查询流程
1. 接收查询 sql
2. 查询缓存，如果未命中，则会继续进行下一步
3. 解析器（parser）解析 sql 语句，生成解析树
4. 预处理器进行预处理，检查数据表和数据列是否存在，解析名字和别名等
5. 查询优化器（）进行优化处理，生成最优的执行计划
6. 调用存储引擎进行查询
7. 返回结果（生成第一条查询结果时就开始逐步返回，所以服务器不需要暂存结果）

mysql 缓存

开启缓存 my.ini 文件中修改

query_cache_type=ON
query_cache_size=1024

由于 mysql 的缓存时基于表的，所以表中的数据有任何更新，就会使缓存失效

执行计划的 type（访问类型）

system：
const：根据主键活唯一索引查询到一条数据
eq_ref：根据唯一索引访问
ref：根据非唯一索引访问
range：根据索引访问一定的范围
index：根据索引访问全表
ALL：无索引访问全表

mysql 慢查询日志

开启：修改 my.ini 中的以下配置

slow_query_log=ON  
long_query_time=0.1  
log_queries_not_using_index=ON

Access MySQL from C （用 C 访问 MySQL 数据库）

This blog is mainly a collection of study notes and some simple tryout examples. For more details, refer to "Beginning Linux Programming", Chapter 8.

Most Commonly Used APIs for Accessing MySQL:

MYSQL *mysql_init(MYSQL *);

MYSQL *mysql_real_connect(MYSQL *connection,

const char *server_host,

const char *sql_user_name,

const char *sql_password,

const char *db_name,

unsigned int port_number,

const char *unix_socket_name,

unsigned int flags);

void mysql_close(MYSQL *connection);

int mysql_options(MYSQL *connection, enum option_to_set,

const char *argument);

int mysql_query(MYSQL *connection, const char *query);

my_ulonglong mysql_affected_rows(MYSQL *connection);

unsigned int mysql_errno(MYSQL *connection);

char *mysql_error(MYSQL *connection);

MYSQL_RES *mysql_store_result(MYSQL *connection);

my_ulonglong mysql_num_rows(MYSQL_RES *result);

MYSQL_ROW mysql_fetch_row(MYSQL_RES *result);	

void mysql_data_seek(MYSQL_RES *result, my_ulonglong offset);

MYSQL_ROW_OFFSET mysql_row_tell(MYSQL_RES *result);

MYSQL_ROW_OFFSET mysql_row_seek(MYSQL_RES *result, MYSQL_ROW_OFFSET offset);

void mysql_free_result(MYSQL_RES *result);

unsigned int mysql_field_count(MYSQL *connection);

MYSQL_FIELD *mysql_fetch_field(MYSQL_RES *result);

char *mysql_get_client_info(void);

char *mysql_get_host_info(MYSQL *connection);

char *mysql_get_server_info(MYSQL *connection);

char *mysql_info(MYSQL *connection);

int mysql_select_db(MYSQL *connection, const char *dbname);

int mysql_shutdown(MYSQL *connection, enum mysql_enum_shutdown_level);

Example1: how to connect to a mysql server

#include <stdlib.h>
#include <stdio.h>
#include "mysql.h"

int main(int argc, char *argv[])
{
        MYSQL *conn_ptr;

        conn_ptr = mysql_init(NULL);
	if (!conn_ptr)
	{
                fprintf(stderr, "mysql_init failed\n");
                exit(EXIT_FAILURE);
        }

        conn_ptr = mysql_real_connect(conn_ptr, "localhost", "chenqi", "helloworld",
                                      "test", 0, NULL, 0);

	if (conn_ptr)
	{
         	printf("Connection Success \n");
	}
        else
	{
         	printf("Connection failed \n");
	}

        mysql_close(conn_ptr);

	exit(EXIT_SUCCESS);
}

gcc -I/usr/include/mysql connect1.c -L/usr/lib/mysql -lmysqlclient -o connect1

Example2: how to handle errors

#include <stdlib.h>
#include <stdio.h>
#include "mysql.h"

int main(int argc, char *argv[])
{
        MYSQL conn;
        mysql_init(&conn);
        if (mysql_real_connect(&conn, "localhost", "chenqi",
	                       "i do not know", "test", 0, NULL, 0))
	{
                printf("Connection Success \n");
                mysql_close(&conn);
        }
        else
        {
                fprintf(stderr, "Connection Failed \n");
                if (mysql_errno(&conn))
                {
                        fprintf(stderr, "Connection error %d: %s \n",
                                mysql_errno(&conn), mysql_error(&conn));
                }
        }
        exit(EXIT_SUCCESS);
}

result:

chenqi@chenqi-laptop ~/MyPro/Database/access_with_c $ ./connect2
Connection Failed
Connection error 1045: Access denied for user ''chenqi''@''localhost'' (using password: YES)

Example3: how to insert and update

/* insert a row into the table children */
                ret = mysql_query(&conn, "insert into children(fname, age) values(''James'', 23)");
                if (!ret)
                {
                        printf("Inserted %lu rows \n",
                               (unsigned long)mysql_affected_rows(&conn));
                }
                else
                {
                        fprintf(stderr, "Insert error %d: %s \n",
                                mysql_errno(&conn), mysql_error(&conn));
                }
                /* update a row in the table children */
                ret = mysql_query(&conn, "update children set age = 24 where fname = ''James''");
                if (!ret)
                {
                        printf("Update %lu rows \n",
                               (unsigned long)mysql_affected_rows(&conn));
                }
                else
                {
                        fprintf(stderr, "Update error %d: %s \n",
                                mysql_errno(&conn), mysql_error(&conn));
                }

result:

chenqi@chenqi-laptop ~/MyPro/Database/access_with_c $ ./insert-update
Connection Success
Inserted 1 rows
Update 2 rows

Example4: how to retrieve data into C application

Step1: Issue the query (mysql_query)
Step2: Retrieve the data (mysql_store_result, mysql_use_result)
Step3: Process the data (mysql_fetch_row)
Step4: Tidy up if necessary (mysql_free_result)

For a large data set, mysql_use_result should be considered, because it uses less storage.

MySQL, like other SQL databases, gives back two sorts of data:
1. The retrieved information from the table, namely the column data
2. Data about the data, so-called metadata, such as column types and names

#include <stdlib.h>
#include <stdio.h>

#include "mysql.h"

MYSQL my_connection;
MYSQL_RES *res_ptr;
MYSQL_ROW sqlrow;

void display_row()
{
	unsigned int field_count = 0;
	while(field_count < mysql_field_count(&my_connection))
	{
		if (sqlrow[field_count])
			printf("%12s", sqlrow[field_count]);
		else
			printf("%12s", "NULL");
		field_count++;
	}
	printf("\n");
}

/* show metadata of each column */
void display_header()
{
	MYSQL_FIELD *field_ptr;

	printf("Column Details:\n");
	while((field_ptr = mysql_fetch_field(res_ptr)) != NULL)
	{
		printf("\t Name: %s\n", field_ptr->name);
		printf("\t Type: ");
		if (IS_NUM(field_ptr->type))
		{
			printf("Numeric Field\n");
		}
		else
		{
			switch(field_ptr->type)
			{
			case FIELD_TYPE_VAR_STRING:
				printf("VARCHAR\n");
				break;
			case FIELD_TYPE_LONG:
				printf("LONG\n");
				break;
			default:
				printf("Type is %d, check in mysql_com.h\n", field_ptr->type);
			}
		} /* else */
		printf("\t Max_width %ld\n", field_ptr->max_length);
		if (field_ptr->flags & AUTO_INCREMENT_FLAG)
		{
			printf("\t Auto increments\n");
		}
		printf("\n");
	}	  /* while */
}

int main(int argc, char *argv[])
{
	int ret;
	mysql_init(&my_connection);
	if (mysql_real_connect(&my_connection, "localhost", "chenqi",
			       "helloworld", "test", 0, NULL, 0))
	{
		printf("Conncetion Success\n");
		ret = mysql_query(&my_connection, "select * from children");
		if (ret)	/* error */
		{
			printf("select error: %s\n", mysql_error(&my_connection));
		}
		else		/* ok */
		{
			res_ptr = mysql_store_result(&my_connection); /* mysql_use_result for an alternative */
			if (res_ptr)
			{
				printf("Retrieved %lu rows \n", (unsigned long)mysql_num_rows(res_ptr));
				display_header();
				while (sqlrow = mysql_fetch_row(res_ptr))
				{
///					printf("Fetching data ... \n");
					display_row();
				}
				if (mysql_errno(&my_connection))
				{
					fprintf(stderr, "Retrieve error: %s\n", mysql_error(&my_connection));
				}
				mysql_free_result(res_ptr); /* res_ptr != NULL */
			}
		}
		mysql_close(&my_connection);
	}
	else
	{
		fprintf(stderr, "Connection Failed\n");
		if (mysql_errno(&my_connection))
		{
			fprintf(stderr, "Connection error %d: %s\n",
				mysql_errno(&my_connection), mysql_error(&my_connection));
		}
		exit(EXIT_FAILURE);
	}
	exit(EXIT_SUCCESS);
}

Result:

chenqi@chenqi-laptop ~/MyPro/Database/access_with_c $ ./select1
Conncetion Success
Retrieved 12 rows
Column Details:
         Name: childno
         Type: Numeric Field
         Max_width 2
         Auto increments

         Name: fname
         Type: VARCHAR
         Max_width 8

         Name: age
         Type: Numeric Field
         Max_width 2

           1       Jenny          21
           2        Feby          25
           3    Chandler          12
           4      Monica          23
           5      Rachel          21
           6        Ross           2
           7         Joy          11
           8        Emma          11
           9       Gavin          14
          10      Andrew          21
          12       James          24
          13         Tom          13

Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)

ERROR 2002 (HY000): Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)

原因：系统盘满了

[root@localhost opt]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
18G 17G 0 100% /
tmpfs 504M 0 504M 0% /dev/shm
/dev/sda1 477M 80M 372M 18% /boot
[root@localhost opt]#

解决：

删除大文件后，重启系统解决

[root@localhost mysql]# /opt/lampp/lampp status
Version: XAMPP for Linux 1.8.3-3
Apache is not running.
MySQL is not running.
ProFTPD is running.

df: 未处理文件系统
[root@localhost opt]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root
18G 17G 0 100% /
tmpfs 504M 0 504M 0% /dev/shm
/dev/sda1 477M 80M 372M 18% /boot
[root@localhost opt]#

[root@localhost ~]# /opt/lampp/lampp status
Version: XAMPP for Linux 1.8.3-3
Apache is not running.
MySQL is running.
ProFTPD is running.

转

xampp 无法启动mysql 找不到mysql.sock

(2016-02-24 23:21:24)

转载▼

分类：技术

出现的问题：
如果xampp中的mysql启动不了，出现ERROR 2002 (HY000): Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)报错，
停止xampp的时候报：
-bash-4.1# /opt/lampp/lampp stop
Stopping XAMPP for Linux 1.8.2-6...
XAMPP: Stopping Apache...ok.
XAMPP: Stopping MySQL...ok.
XAMPP: Stopping ProFTPD...kill: usage: kill [-s sigspec | -n signum | -sigspec] pid | jobspec ... or kill -l [sigspec]
fail.

解决办法：
如果网上一些方法不好用的话，可以试试以下方法：
1. 确定系统盘是否满了
#df -h
2. 删除/opt/lampp目录中的pid文件（删掉后xampp重启时会重建，如果不放心，可以先备份lampp目录）
删除mysql相关缓存：
#rm -rf /opt/lampp/var/mysql/VM_*
删除proftp相关缓存：
#rm -rf /opt/lampp/var/proftpd.pid
如果找不到pid文件，可以搜一下：
#find /opt/lampp -name ''*.pid''

Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''

MySQL已经被我移到数据盘了，本地连接数据库会报错：Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''

但是远程是可以连接的，my.cnf设置mysql的根目录也改成了数据盘的地址，还要在加上client的参数，设置如下：

[client]
socket = /home/data/mysql/mysql.sock

之后重启下mysql就可以了

CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’

亲，是不是忘记了开MysqL服务，service MysqLd start

今天关于MySQL和mysql怎么读的分享就到这里，希望大家有所收获，若想了解更多关于Access MySQL from C （用 C 访问 MySQL 数据库）、Can''t connect to local MySQL server through socket ''/opt/lampp/var/mysql/mysql.sock'' (2)、Can''t connect to local MySQL server through socket ''/var/lib/mysql/mysql.sock''、CentOS yum安装mysql后 Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’等相关知识，可以在本站进行查询。

本文标签：