cnzz统计代码放在网站,万网买好域名后如何开通网站,wordpress自适应视频,酒水招商网站大全最后一行有我自己的my.ini文件 数据库输入中文数据时会变为乱码#xff0c;
这个时候#xff0c;我们为每个数据库设置字符集#xff0c;太过于麻烦#xff0c;为数据库单独设置重启后又会消失
Set character_set_database’utf8’;
Set character_set_server’utf8’;
…最后一行有我自己的my.ini文件 数据库输入中文数据时会变为乱码
这个时候我们为每个数据库设置字符集太过于麻烦为数据库单独设置重启后又会消失
Set character_set_database’utf8’;
Set character_set_server’utf8’;
先查看当前数据库参数
输入代码
Show variables like ‘char%’; set character_set_databaseutf8; Show variables like ‘char%’; 为了确保新创建的数据库和表默认使用UTF-8编码可以在MySQL的配置文件my.cnf或my.ini中添加或修改以下参数
这样的方式可以让今后所有的数据库新建时默认utf-8
[client]
default-character-setutf8mb4[mysql]
default-character-setutf8mb4[mysqld]
character-set-serverutf8mb4
collation-serverutf8mb4_unicode_ci 目前只能针对某一数据库来进行所以直接使用就行
ALTER DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
通过以上步骤就可以将整个数据库都改为UTF-8支持中文 my.ini在Unix/Linux系统中通常称为my.cnf是MySQL数据库的配置文件用于指定MySQL服务器的各种设置包括字符集、端口、数据目录等。根据操作系统和安装方式的不同这个文件可能位于不同的位置。以下是寻找my.ini或my.cnf文件的一些常见位置
Windows 系统 默认安装路径 C:\ProgramData\MySQL\MySQL Server X.Y\my.ini 或C:\Program Files\MySQL\MySQL Server X.Y\my.ini其中X.Y代表您的MySQL版本号。 通过MySQL Installer安装如果您使用的是MySQL Installer来安装MySQL那么配置文件可能会被放置在上述默认位置之一。 自定义路径如果在安装过程中指定了不同的配置文件路径则需要参考当时的安装记录或文档。 查看当前使用的配置文件路径您可以通过以下命令查看MySQL正在使用的配置文件的位置。 mysql SHOW VARIABLES LIKE explicit_defaults_for_timestamp; 但是更直接的方式是在命令行下运行 mysqld --verbose --help | findstr Default options -A 10
Unix/Linux 系统 标准位置 /etc/my.cnf/etc/mysql/my.cnf~/.my.cnf用户特定配置 其他可能位置MySQL服务启动时会按照特定顺序查找配置文件您可以使用以下命令找到MySQL实际加载的配置文件 mysqld --verbose --help | grep -A 1 Default options 这个命令将列出MySQL搜索配置文件的顺序和位置。 Docker容器内如果MySQL是在Docker容器中运行配置文件可能是通过挂载卷的方式提供的或者是在容器内部的一个自定义位置。您可以进入容器检查 docker exec -it container_name bash
查找配置文件的具体步骤 Windows: 打开文件资源管理器。导航至上述提到的可能位置之一。如果找不到请尝试使用搜索功能查找my.ini或my.cnf文件。 Linux/Unix: 打开终端。使用locate或find命令查找配置文件 sudo updatedb # 更新数据库以确保locate能找到最新的文件
locate my.cnf 或者 find / -name my.cnf 2/dev/null
一旦找到了正确的配置文件就可以按照之前提供的指南进行编辑以修改字符集设置。记得在修改后重启MySQL服务使更改生效。 以下为我的my.ini全文
原文链接各个系统如何寻找数据库的my.ini并进行修改-CSDN博客 # Other default tuning values
# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------
# Generated by the MySQL Server Instance Configuration Wizard
#
#
# Installation Instructions
# ----------------------------------------------------------------------
#
# On Linux you can copy this file to /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
# (localstatedir for this installation) or to
# ~/.my.cnf to set user-specific options.
#
# On Windows, when MySQL has been installed using MySQL Installer you
# should keep this file in the ProgramData directory of your server
# (e.g. C:\ProgramData\MySQL\MySQL Server X.Y). To make sure the server
# reads the config file, use the startup option --defaults-file.
#
# To run the server from the command line, execute this in a
# command line shell, e.g.
# mysqld --defaults-fileC:\Program Files\MySQL\MySQL Server X.Y\my.ini
#
# To install the server as a Windows service manually, execute this in a
# command line shell, e.g.
# mysqld --install MySQLXY --defaults-fileC:\Program Files\MySQL\MySQL Server X.Y\my.ini
#
# And then execute this in a command line shell to start the server, e.g.
# net start MySQLXY
#
#
# Guidelines for editing this file
# ----------------------------------------------------------------------
#
# In this file, you can use all long options that the program supports.
# If you want to know the options a program supports, start the program
# with the --help option.
#
# More detailed information about the individual options can also be
# found in the manual.
#
# For advice on how to change settings please see
# https://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
#
#
# CLIENT SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by MySQL client applications.
# Note that only client applications shipped by MySQL are guaranteed
# to read this section. If you want your own MySQL client program to
# honor these values, you need to specify it as an option during the
# MySQL client library initialization.
#
[client]default_character-setutf8mb4# pipe# socketMYSQLport3306[mysql]
no-beepdefault-character-setutf8mb4# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this
# file.
#
# server_type3
[mysqld]
character_set_serverutf8mb4
collation_serverutf8mb4_unicode_ci # The next three options are mutually exclusive to SERVER_PORT below.
# skip-networking
# enable-named-pipe
# shared-memory# shared-memory-base-nameMYSQL# The Pipe the MySQL Server will use
# socketMYSQL# The access control granted to clients on the named pipe created by the MySQL Server.
# named-pipe-full-access-group# The TCP/IP Port the MySQL Server will listen on
port3306# Path to installation directory. All paths are usually resolved relative to this.
# basedirC:/Program Files/MySQL/MySQL Server 5.7/# Path to the database root
datadirC:/ProgramData/MySQL/MySQL Server 5.7/Data# The default character set that will be used when a new schema or table is
# created and no character set is defined
# character-set-server# The default storage engine that will be used when create new tables when
default-storage-engineINNODB# The current server SQL mode, which can be set dynamically.
# Modes affect the SQL syntax MySQL supports and the data validation checks it performs. This
# makes it easier to use MySQL in different environments and to use MySQL together with other
# database servers.
sql-modeONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION# General and Slow logging.
log-outputFILEgeneral-log0general_log_fileDESKTOP-QNC8DKQ.logslow-query-log1slow_query_log_fileDESKTOP-QNC8DKQ-slow.loglong_query_time10# Error Logging.
log-errorDESKTOP-QNC8DKQ.err# ***** Group Replication Related *****
# Specifies the base name to use for binary log files. With binary logging
# enabled, the server logs all statements that change data to the binary
# log, which is used for backup and replication.
# log-bin# ***** Group Replication Related *****
# Specifies the server ID. For servers that are used in a replication topology,
# you must specify a unique server ID for each replication server, in the
# range from 1 to 2^32 − 1. Unique means that each ID must be different
# from every other ID in use by any other replication source or replica.
server-id1# ***** Group Replication Related *****
# Indicates how table and database names are stored on disk and used in MySQL.
# Value 0 Table and database names are stored on disk using the lettercase specified in the CREATE
# TABLE or CREATE DATABASE statement. Name comparisons are case-sensitive. You should not
# set this variable to 0 if you are running MySQL on a system that has case-insensitive file
# names (such as Windows or macOS). If you force this variable to 0 with
# --lower-case-table-names0 on a case-insensitive file system and access MyISAM tablenames
# using different lettercases, index corruption may result.
# Value 1 Table names are stored in lowercase on disk and name comparisons are not case-sensitive.
# MySQL converts all table names to lowercase on storage and lookup. This behavior also applies
# to database names and table aliases.
# Value 2 Table and database names are stored on disk using the lettercase specified in the CREATE TABLE
# or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons
# are not case-sensitive. This works only on file systems that are not case-sensitive! InnoDB
# table names and view names are stored in lowercase, as for lower_case_table_names1.
lower_case_table_names1# This variable is used to limit the effect of data import and export operations, such as
# those performed by the LOAD DATA and SELECT ... INTO OUTFILE statements and the
# LOAD_FILE() function. These operations are permitted only to users who have the FILE privilege.
secure-file-privC:/ProgramData/MySQL/MySQL Server 5.7/Uploads# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections151# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable open-files-limit in
# section [mysqld_safe]
table_open_cache2000# Defines the maximum size of internal in-memory temporary tables created
# by the MEMORY storage engine and, as of MySQL 8.0.28, the TempTable storage
# engine. If an internal in-memory temporary table exceeds this size, it is
# automatically converted to an on-disk internal temporary table.
tmp_table_size51M#*** MyISAM Specific options
# The maximum size of the temporary file that MySQL is permitted to use while re-creating a
# MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA). If the file size would be
# larger than this value, the index is created using the key cache instead, which is slower.
# The value is given in bytes.
myisam_max_sort_file_size2146435072# The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE
# or when creating indexes with CREATE INDEX or ALTER TABLE.
myisam_sort_buffer_size92M# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if youre not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size8M# Each thread that does a sequential scan for a MyISAM table allocates a buffer
# of this size (in bytes) for each table it scans. If you do many sequential
# scans, you might want to increase this value, which defaults to 131072. The
# value of this variable should be a multiple of 4KB. If it is set to a value
# that is not a multiple of 4KB, its value is rounded down to the nearest multiple
# of 4KB.
read_buffer_size128K# This variable is used for reads from MyISAM tables, and, for any storage engine,
# for Multi-Range Read optimization.
read_rnd_buffer_size256K#*** INNODB Specific options ***
# innodb_data_home_dir# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
# skip-innodb# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit1# The size in bytes of the buffer that InnoDB uses to write to the log files on
# disk. The default value changed from 8MB to 16MB with the introduction of 32KB
# and 64KB innodb_page_size values. A large log buffer enables large transactions
# to run without the need to write the log to disk before the transactions commit.
# Thus, if you have transactions that update, insert, or delete many rows, making
# the log buffer larger saves disk I/O.
innodb_log_buffer_size16M# The size in bytes of the buffer pool, the memory area where InnoDB caches table
# and index data. The default value is 134217728 bytes (128MB). The maximum value
# depends on the CPU architecture; the maximum is 4294967295 (232-1) on 32-bit systems
# and 18446744073709551615 (264-1) on 64-bit systems. On 32-bit systems, the CPU
# architecture and operating system may impose a lower practical maximum size than the
# stated maximum. When the size of the buffer pool is greater than 1GB, setting
# innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on
# a busy server.
innodb_buffer_pool_size128M# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size48M# Defines the maximum number of threads permitted inside of InnoDB. A value
# of 0 (the default) is interpreted as infinite concurrency (no limit). This
# variable is intended for performance tuning on high concurrency systems.
# InnoDB tries to keep the number of threads inside InnoDB less than or equal to
# the innodb_thread_concurrency limit. Once the limit is reached, additional threads
# are placed into a First In, First Out (FIFO) queue for waiting threads. Threads
# waiting for locks are not counted in the number of concurrently executing threads.
innodb_thread_concurrency17# The increment size (in MB) for extending the size of an auto-extend InnoDB system tablespace file when it becomes full.
innodb_autoextend_increment64# The number of regions that the InnoDB buffer pool is divided into.
# For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency,
# by reducing contention as different threads read and write to cached pages.
innodb_buffer_pool_instances8# Determines the number of threads that can enter InnoDB concurrently.
innodb_concurrency_tickets5000# Specifies how long in milliseconds (ms) a block inserted into the old sublist must stay there after its first access before
# it can be moved to the new sublist.
innodb_old_blocks_time1000# When this variable is enabled, InnoDB updates statistics during metadata statements.
innodb_stats_on_metadata0# When innodb_file_per_table is enabled (the default in 5.6.6 and higher), InnoDB stores the data and indexes for each newly created table
# in a separate .ibd file, rather than in the system tablespace.
innodb_file_per_table1# Use the following list of values: 0 for crc32, 1 for strict_crc32, 2 for innodb, 3 for strict_innodb, 4 for none, 5 for strict_none.
innodb_checksum_algorithm0# If this is set to a nonzero value, all tables are closed every flush_time seconds to free up resources and
# synchronize unflushed data to disk.
# This option is best used only on systems with minimal resources.
flush_time0# The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use
# indexes and thus perform full table scans.
join_buffer_size256K# The maximum size of one packet or any generated or intermediate string, or any parameter sent by the
# mysql_stmt_send_long_data() C API function.
max_allowed_packet4M# If more than this many successive connection requests from a host are interrupted without a successful connection,
# the server blocks that host from performing further connections.
max_connect_errors100# Changes the number of file descriptors available to mysqld.
# You should try increasing the value of this option if mysqld gives you the error Too many open files.
open_files_limit4161# If you see many sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the
# sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization
# or improved indexing.
sort_buffer_size256K# Specify the maximum size of a row-based binary log event, in bytes.
# Rows are grouped into events smaller than this size if possible. The value should be a multiple of 256.
binlog_row_event_max_size8K# If the value of this variable is greater than 0, a replica synchronizes its master.info file to disk.
# (using fdatasync()) after every sync_master_info events.
sync_master_info10000# If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk.
# (using fdatasync()) after every sync_relay_log writes to the relay log.
sync_relay_log10000# If the value of this variable is greater than 0, a replica synchronizes its relay-log.info file to disk.
# (using fdatasync()) after every sync_relay_log_info transactions.
sync_relay_log_info10000# Load mysql plugins at start.plugin_x ; plugin_y.
# plugin_load# The TCP/IP Port the MySQL Server X Protocol will listen on.
# loose_mysqlx_port33060Windows 如何重启数据库
原文链接 Windows 如何重启数据库-CSDN博客
在Windows操作系统上重启MySQL数据库服务可以通过多种方式进行包括使用命令行工具和服务管理控制台。以下是具体步骤
方法一通过命令提示符CMD重启MySQL服务 打开命令提示符
按 Win R 键输入 cmd然后按 Enter 键打开命令提示符窗口。 停止MySQL服务 在命令提示符中输入以下命令以停止MySQL服务。请注意您可能需要管理员权限来执行这些命令。
net stop MySQLX.Y 其中 MySQLX.Y 应替换为您的MySQL服务名称。默认情况下这可能是 MySQL57, MySQL80 等取决于您的安装版本。
启动MySQL服务 输入以下命令以启动MySQL服务
net start MySQLX.Y 方法二通过服务管理控制台重启MySQL服务 打开服务管理控制台
按 Win R 键输入 services.msc然后按 Enter 键打开服务管理控制台。 找到MySQL服务
在服务列表中向下滚动直到找到与MySQL相关的服务项例如 MySQL57, MySQL80 或者您自定义的服务名。 重启MySQL服务
右键点击MySQL服务项选择 重启Restart。如果服务当前正在运行此操作将首先停止服务然后立即重新启动它。 方法三通过MySQL Workbench重启服务如果有安装 如果您安装了MySQL Workbench也可以通过图形界面来重启MySQL服务
打开MySQL Workbench。 在左侧的导航栏中找到并点击 Instance 下的 Startup / Shutdown。 如果服务器当前正在运行您可以点击 Shutdown 来停止服务之后再点击 Startup 来启动服务。 注意事项 管理员权限确保以管理员身份运行命令提示符或服务管理控制台特别是在执行停止和启动服务的操作时。 确认服务名称如果不确定MySQL服务的确切名称可以打开服务管理控制台 (services.msc) 查看所有服务并找到正确的MySQL服务名称。 等待完成服务停止和启动过程可能需要一些时间请耐心等待直至操作完成。 通过以上任意一种方法都可以实现MySQL数据库服务的重启以便让配置更改生效。
