GCP Cloud SQL: MySQL Create View ERROR 1227: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

今日 User 要建立一個 MySQL 的View  Table,但卻出現錯誤訊息


ERROR 1227: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

使用者說，是不是沒有給他足夠的權限，所以無法執行。

環境說明:
1.MySQL 是用 Google Cloud Platform (GCP) 的 Cloud SQL (MySQL 第 2 代 5.7)
2.Cloud SQL 沒有區分帳號可以讀寫哪一個Schema
3.Client 工具用的是 MySQL Workbench 6.2 CE
4.使用者是用 MySQL Workbench  連接到 Cloud SQL 讀取現有 View , 工具就會產出 Create Statement
5.再執行工具產生的Create Statement，就會出錯

處理方式:
當然要加大權限也不是不可以，但更應該確認需求，而不是盲目的加大權限
根據
http://jenyu316.pixnet.net/blog/post/223690152-mysql%E5%9B%9E%E6%87%89----%3A-%231227---access-denied%3B-you-need-%28at-least-o
的說明，只要將以下三行拿掉就可以了。

ALGORITHM = UNDEFINED
DEFINER = `xxxx`@`xx.xx.xx.xx`    << 最主要的原因
SQL SECURITY DEFINER

查MySQL LOCK

查MySQL LOCK

show processlist;
其中的state 欄位會出現 Locked;
依據ID 欄位可以執行 kill ID編號; 
進行刪除。

MySQL Host is blocked

無法查看此摘要。請 按這裡查看文章。

MySql ibdata1文件太大如何缩小

MySQL 預設是用共享表空间 innodb，因此ibdata1文件會長得很快，最好是在my.ini或my.cnf文件

[mysqld]下增加下面配置 innodb_file_per_table=1 比較好，詳細作法如下

以下文章摘自http://blog.okbase.net/haobao/archive/1213.html

MySql innodb如果是共享表空间，ibdata1文件越来越大，达到了30多个G，对一些没用的表进行清空：
truncate table xxx;
然后optimize table xxx; 没有效果
因为对共享表空间不起作用。
mysql ibdata1存放数据，索引等，是MYSQL的最主要的数据。

如果不把数据分开存放的话，这个文件的大小很容易就上了G，甚至几十G。对于某些应用来说，并不是太合适。因此要把此文件缩小。
无法自动收缩，必须数据导出，删除ibdata1，然后数据导入，比较麻烦，因此需要改为每个表单独的文件。

解决方法：数据文件单独存放(共享表空间如何改为每个表独立的表空间文件)。
步骤如下：

1）备份数据库

从命令行进入MySQL Server 5.0\bin
备份全部数据库，执行命令
D:\>mysqldump -q -umysql -ppassword --add-drop-table --all-databases > c:/all.sql

做完此步后，停止数据库服务。

2）找到my.ini或my.cnf文件
linux下执行 ./mysqld --verbose --help | grep -A 1 'Default options'
会有类似显示：
Default options are read from the following files in the given order:
/etc/my.cnf ~/.my.cnf /usr/local/service/mysql3306/etc/my.cnf

windows环境下可以：
mysqld --verbose --help > mysqlhelp.txt
notepad mysqlhelp.txt
在里面查找Default options，可以看到查找my.ini的顺序，以找到真实目录

3）修改mysql配置文件
打开my.ini或my.cnf文件
[mysqld]下增加下面配置
innodb_file_per_table=1

验证配置是否生效，可以重启mysql后,执行
show variables like '%per_table%'
看看innodb_file_per_table变量是否为ON

4）删除原数据文件

删除原来的ibdata1文件及日志文件ib_logfile*，删除data目录下的应用数据库文件夹(mysql文件夹不要删)

5）还原数据库

启动数据库服务

从命令行进入MySQL Server 5.0\bin
还原全部数据库，执行命令mysql -uusername -pyourpassword < c:/all.sql

经过以上几步后，可以看到新的ibdata1文件就只有几十M了，数据及索引都变成了针对单个表的小ibd文件了，它们在相应数据库的文件夹下面。

查詢MySQL 資料庫的編碼

SHOW CREATE DATABASE DBNAME;

mysql> SHOW CREATE DATABASE DbName;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| DbName   | CREATE DATABASE `DbName` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)



也可以用
use DBNAME;
status;

mysql> use DBNAME;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> status;
--------------
mysql  Ver 14.14 Distrib 5.5.32, for Linux (x86_64) using readline 5.1

Connection id:          313
Current database:       DBNAME
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.32-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:   latin1
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:   latin1
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 50 min 29 sec

Threads: 51  Questions: 62069  Slow queries: 492  Opens: 274  Flush tables: 1  Open tables: 216  Queries per second avg: 20.491
--------------

第二個指令可以看的比較細

MySQL Workbench 5.2 CE

執行MySQL Workbench 5.2 CE update or delete 時出現以下訊息
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor -> Query Editor and reconnect.
到工能表的Edit>Preferences>SQL Queries> General > 取消 "Safe Updates".Forbid UPDATEs and DELETEs whih no key in WHERE clause or no LIMIT clause. Requires a reconnection.
再重連接DB 就可以。

ibdata1 用途說明

这个文件超级大， 查了一下， 大概的作用如下

是储存的格式

INNODB类型数据状态下，

ibdata用来储存文件的数据

而库名的文件夹里面的那些表文件只是结构而已

由于mysql4.1默认试innodb，所以这个文件默认就存在了http://man.chinaunix.net/database/mysql/inonodb_zh/2.htm 这个链接试innodb的中文参考， innodb的东西可以在my.ini中设置

innodo中文参考全文如下

InnoDB 启动选项

为了在 MySQL-Max-3.23 中使用 InnoDB 表，你必须在配置文件‘my.cnf’或‘my.ini’（WINDOWS系统）中的 [mysqld] 区中详细指定配置参数。

作为最小设置，在 3.23 中你必须在 innodb_data_file_path 上指定数据文件名能及大小。如果在‘my.cnf’中没有指定innodb_data_home_dir，系统将在 MySQL 的 datadir 目录下创建数据文件。如果将 innodb_data_home_dir 设为一个空串，那可以在 innodb_data_file_path 中给定一个绝对路径。在 MySQL-4.0 中可以不设定 innodb_data_file_path ：MySQL-4.0 将默认地在 datadir 目录下建立一个 10 MB 大小自扩充(auto-extending)的文件‘ibdata1’(在MySQL-4.0.0 与 4.0.1 中数据文件的大小为 64 MB 并且是非自扩充的(not auto-extending))。

为了得到更好的性能你必须所示的例子明确地设定 InnoDB 启动参数。

从 3.23.50 版和 4.0.2 版开始，InnoDB 允许在 innodb_data_file_path 中设置的最一个数据文件描述为 auto-extending。 innodb_data_file_path 语法如下所示：

pathtodatafile:sizespecification;pathtodatafile:sizespec;...

...;pathtodatafile:sizespec[:autoextend[:max:sizespecification]]

如果用 autoextend 选项描述最后一个数据文件，当 InnoDB 用尽所有表自由空间后将会自动扩充最后一个数据文件，每次增量为 8 MB。示例：

innodb_data_home_dir =

innodb_data_file_path = /ibdata/ibdata1:100M:autoextend

指定 InnoDB 只建立一个最初大小为 100 MB 并且当表空间被用尽时以 8MB 每块增加的数据文件。如果硬盘空间不足，可以再添加一个数据文件并将其放在其它的硬盘中。 举例来说：先检查硬盘空间的大小，设定 ibdata1 文件使它接近于硬盘空余空间大小并为 1024 * 1024 bytes (= 1 MB)的倍数， 将 ibdata1 明确地指定在 innodb_data_file_path 中。在此之后可以添加另一个数据文件：

innodb_data_home_dir =

innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

注意：设定文件大小时一定要注意你的OS是否有最大文件尺寸为2GB的限制！InnoDB是不会注意你的OS文件尺寸限制的， 在一些文件系统中你可能要设定最大容量限制：

innodb_data_home_dir =

innodb_data_file_path = /ibdata/ibdata1:100M:autoextend:max:2000M

　

一个简单的 my.cnf 例子。 假设你的计算机有 128 MB RAM 和一个硬盘。下面的例子是为了使用 InnoDB 而在 my.cnf 或 my.ini 文件中可能所作的一些配置。我们假设你运行的是 MySQL-Max-3.23.50 及以上版本，或 MySQL-4.0.2 及以上版本。

这个示例适合大部分不需要将 InnoDB 数据文件和日志文件放在几个盘上的 Unix 和 Windows 用户。这个例子在 MySQL 的datadir 目录(典型的为 /mysql/data)中创建一个自扩充(auto-extending)的数据文件 ibdata1 和两个 InnoDB 运行日志文件ib_logfile0 和 ib_logfile1 以及 ib_arch_log_0000000000 档案文件。

[mysqld]

#在这里加入其它 的 MySQL 服务器配置

#...

# 数据文件必须

# 能够容下数据与索引

# 确定有足够的

# 磁盘空间

innodb_data_file_path = ibdata1:10M:autoextend

# 设置缓冲池的大小为

# 你的主内存大小的

# 50 - 80 %

set-variable = innodb_buffer_pool_size=70M

set-variable = innodb_additional_mem_pool_size=10M

# 设置日志文件的大小约为

# 缓冲池(buffer pool)

# 大小的 25 %

set-variable = innodb_log_file_size=20M

set-variable = innodb_log_buffer_size=8M

# 如果丢失最近几个事务影响

# 不大的话可以设置

# .._flush_log_at_trx_commit = 0

innodb_flush_log_at_trx_commit=1

InnoDB 不会自己建立目录，必须自己使用操作系统命令建立相应的目录。检查你的 MySQL 服务程序在 datadir 目录里 有足够的权限建立文件。

注意：在某些文件系统中 数据文件大小必须小于2G！ 所有运行日志文件的大小总和必须小于 2G 或 4G,这依赖于具体的 MySQL 系统版本。 数据文件的总和必须大于等于 10 MB.

当第一次建立 InnoDB 数据库时，建议最好以命令行方式启动 MySQL 服务。这样 InnoDB 数据库建立时的提示信息将在屏幕上显示，从而可以看到建立过程。 下面第 3 节所示就是 InnoDB 数据库建立时的屏幕显示。例如，在 Windows 下使用下列指令启动 mysqld-max.exe ：

your-path-to-mysqld>mysqld-max --console

　

在 Windows 系统下 my.cnf 或 my.ini 放在哪里？规则如下 ：

只能存在一个 my.cnf 或 my.ini 文件

my.cnf 文件必须放在 C: 的根目录下

my.ini 文件必须放在 WINDIR 目录下，例：C:\WINDOWS 或 C:\WINNT。可以使用 MS-DOS 的 SET 命令查看 WINDIR 目录值

如果你的 PC 使用启动引导程序引导系统而 C: 不是启动磁盘，那只能唯一地使用 my.ini 作为设置文件

　

Unix 下在哪里指定配置文件？在 Unix 下 mysqld 按下列顺序搜索配置文件：

/etc/my.cnf 全局选项

COMPILATION_DATADIR/my.cnf 服务器范围的选项

defaults-extra-file 采用 --defaults-extra-file=.... 设置的默认文件

~/.my.cnf 用户指定文件

COMPILATION_DATADIR 是 MySQL 的数据文件目录，它是在 mysqld 被编译时以 ./configure 设置指定 (典型的是 /usr/local/mysql/data 二进制安装或 /usr/local/var 以源安装)。

　

如果不有确定 mysqld 从哪里读取 my.cnf 或 my.ini，可以在第一命令行上详细指定它的目录：mysqld --defaults-file=your_path_to_my_cnf。

InnoDB 的数据文件目录是对 innodb_data_home_dir 与 innodb_data_file_path 的数据文件名或目录联合 ，如果需要将在它们之间增加一个“/”或“\”。如果关键字 innodb_data_home_dir 没有在 my.cnf 中明确指定，它的默认值为“.”，即目录“./”，这意味着 MySQL 的 datadir of MySQL.

一个高级的 my.cnf 示例。假设你有一台 2 GB RAM 和3个 60 GB 硬盘(路径分别为 "/", "/dr2" 和 “/dr3”)装有 Linux。下面的例子是为了使用 InnoDB 而在 my.cnf 文件中可能所作的一些配置。

注意：InnoDB 不会自己创建文件目录：你必须自己创建它们。使用 Unix 或 MS-DOS mkdir 命令建立相应的数据与日志文件目录。

[mysqld]

#在这里加入其它 的 MySQL 服务器配置

#...

# 如果不使用InnoDB表将一列一行注释去除

# skip-innodb

#

# 数据文件必须

# 能够容下数据与索引

# 确定有足够的

# 磁盘空间

innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend

# 设置缓冲池的大小为

# 你的主内存大小的

# 50 - 80 %，但是

# 在 Linux x86 总内存

# 使用必须小于 2 GB

set-variable = innodb_buffer_pool_size=1G

set-variable = innodb_additional_mem_pool_size=20M

innodb_log_group_home_dir = /dr3/iblogs

# .._log_arch_dir 必须和

# .._log_group_home_dir一样；

# 从 4.0.6开始，可以省略它

innodb_log_arch_dir = /dr3/iblogs

set-variable = innodb_log_files_in_group=3

# 设置日志文件的大小约为

# 缓冲池(buffer pool)

# 大小的 15 %

set-variable = innodb_log_file_size=150M

set-variable = innodb_log_buffer_size=8M

# 如果丢失最近几个事务影响

# 不大的话可以设置

# .._flush_log_at_trx_commit = 0

innodb_flush_log_at_trx_commit=1

set-variable = innodb_lock_wait_timeout=50

#innodb_flush_method=fdatasync

#set-variable = innodb_thread_concurrency=5

注意：我们已在不同的硬盘上放置了两个数据文件， InnoDB 将从数据文件的底部填充表空间。在某些情况下所有的数据被分配到不同的物理硬盘中会提高数据库的性能。 将日志文件与数据文件分别放在不同的物理硬盘中对提高性能通常是很有益的。你同样可以使用一个 RAW 磁盘分区( raw disk partitions(raw devices)) 作为数据文件， 在一些 Unixe 系统中这将提高 I/O 能力。 如何在 my.cnf 中详细指定它们请查看第 12.1 节。

警告：在 Linux x86 上必须小心不能将内存使用设置太高， glibc 会把进程堆增长到线程堆栈之上，这将会使服务器崩溃。下面的接近或超过于 2G 将会很危险：

innodb_buffer_pool_size + key_buffer +

max_connections * (sort_buffer + record_buffer) + max_connections * 2 MB

每个线程将使用 2MB(MySQL AB 二进制版本为 256 KB)的堆栈，在最坏的环境下还会使用 sort_buffer + record_buffer 的附加内存。

　

如何调整其它的 mysqld 服务器参数？查看 MySQL 用户手册可以得到更详细的信息。适合大多数用户的典型参数如下所示：

skip-locking

set-variable = max_connections=200

set-variable = record_buffer=1M

set-variable = sort_buffer=1M

# 设置索引缓冲(key_buffer)大小为

# 你的 RAM 的 5 - 50% ，这主要依赖于

# 系统中 MyISAM 表使用量。

# 但是必须保证索引缓冲(key_buffer)与 InnoDB

# 的缓冲池(buffer pool)大小总和

# 小于 RAM 的 80%。

set-variable = key_buffer=...

　

注意：在 my.cnf 文件中有些参数是为了设置数字的，它们的设置格式为：set-variable = innodb... = 123，而其它(字符串和逻辑型)的采用另一设置格式：innodb_... = ... .

db_data_home_dir:这是InnoDB表的目录共用设置。如果没有在 my.cnf 进行设置，InnoDB 将使用MySQL的 datadir 目录为缺省目录。如果设定一个空字串,可以在 innodb_data_file_path 中设定绝对路径。

innodb_data_file_path:单独指定数据文件的路径与大小。数据文件的完整路径由 innodb_data_home_dir 与这里所设定值的组合。 文件大小以 MB 单位指定。因此在文件大小指定后必有“M”。 InnoDB 也支持缩写“G”， 1G = 1024M。从 3.23.44 开始，在那些支持大文件的操作系统上可以设置数据文件大小大于 4 GB。而在另一些操作系统上数据文件必须小于 2 GB。数据文件大小总和至少要达到 10 MB。在 MySQL-3.23 中这个参数必须在 my.cnf 中明确指定。在 MySQL-4.0.2 以及更新版本中则不需如此，系统会默认在 MySQL 的 datadir 目录下创建一个 16 MB 自扩充(auto-extending)的数据文件 ibdata1。你同样可以使用一个 原生磁盘分区(RAW raw disk partitions(raw devices)) 作为数据文件， 如何在 my.cnf 中详细指定它们请查看第 12.1 节。

innodb_mirrored_log_groups:为了保护数据而设置的日志文件组的拷贝数目，默认设置为 1。在 my.cnf 中以数字格式设置。

innodb_log_group_home_dir:InnoDB 日志文件的路径。必须与 innodb_log_arch_dir 设置相同值。 如果没有明确指定将默认在 MySQL 的 datadir 目录下建立两个 5 MB 大小的 ib_logfile... 文件。

innodb_log_files_in_group:日志组中的日志文件数目。InnoDB 以环型方式(circular fashion)写入文件。数值 3 被推荐使用。在 my.cnf 中以数字格式设置。

innodb_log_file_size:日志组中的每个日志文件的大小(单位 MB)。如果 n 是日志组中日志文件的数目，那么理想的数值为 1M 至下面设置的缓冲池(buffer pool)大小的 1/n。较大的值，可以减少刷新缓冲池的次数，从而减少磁盘 I/O。但是大的日志文件意味着在崩溃时需要更长的时间来恢复数据。 日志文件总和必须小于 2 GB，3.23.55 和 4.0.9 以上为小于 4 GB。在 my.cnf 中以数字格式设置。

innodb_log_buffer_size:InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至 8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。 因此，如果有大的事务处理，设置大的日志缓冲可以减少磁盘I/O。 在 my.cnf 中以数字格式设置。

innodb_flush_log_at_trx_commit:通常设置为 1，意味着在事务提交前日志已被写入磁盘， 事务可以运行更长以及服务崩溃后的修复能力。如果你愿意减弱这个安全，或你运行的是比较小的事务处理，可以将它设置为 0 ，以减少写日志文件的磁盘 I/O。这个选项默认设置为 0。

innodb_log_arch_dir:The directory where fully written log files would be archived if we used log archiving. 这里设置的参数必须与 innodb_log_group_home_dir 相同。 从 4.0.6 开始，可以忽略这个参数。

innodb_log_archive:这个值通常设为 0。 既然从备份中恢复(recovery)适合于 MySQL 使用它自己的 log files，因而通常不再需要 archive InnoDB log files。这个选项默认设置为 0。

innodb_buffer_pool_size:InnoDB 用来高速缓冲数据和索引内存缓冲大小。 更大的设置可以使访问数据时减少磁盘 I/O。在一个专用的数据库服务器上可以将它设置为物理内存的 80 %。 不要将它设置太大，因为物理内存的使用竞争可能会影响操作系统的页面调用。在 my.cnf 中以数字格式设置。

innodb_additional_mem_pool_size:InnoDB 用来存储数据字典(data dictionary)信息和其它内部数据结构(internal data structures)的存储器组合(memory pool)大小。理想的值为 2M，如果有更多的表你就需要在这里重新分配。如果 InnoDB 用尽这个池中的所有内存，它将从操作系统中分配内存，并将错误信息写入 MySQL 的错误日志中。在 my.cnf 中以数字格式设置。

innodb_file_io_threads:InnoDB 中的文件 I/O 线程。 通常设置为 4，但是在 Windows 下可以设定一个更大的值以提高磁盘 I/O。在 my.cnf 中以数字格式设置。

innodb_lock_wait_timeout:在回滚(rooled back)之前，InnoDB 事务将等待超时的时间(单位 秒)。InnoDB 会自动检查自身在锁定表与事务回滚时的事务死锁。如果使用 LOCK TABLES 命令，或在同一个事务中使用其它事务安全型表处理器(transaction safe table handlers than InnoDB)，那么可能会发生一个 InnoDB 无法注意到的死锁。在这种情况下超时将用来解决这个问题。这个参数的默认值为 50 秒。在 my.cnf 中以数字格式设置。

innodb_flush_method:这个参数仅仅与 Unix 相关。这个参数默认值为 fdatasync。 另一个设置项为 O_DSYNC。这仅仅影响日志文件的转储，在 Unix 下以 fsync 转储数据。InnoDB 版本从 3.23.40b 开始，在 Unix 下指定 fdatasync 为使用 fsync 方式、指定 O_DSYNC 为使用 O_SYNC 方式。由于这在某些 Unix 环境下还有些问题所以在 'data' versions 并没有被使用。

innodb_force_recovery:警告：此参数只能在你希望从一个被损坏的数据库中转储(dump)数据的紧急情况下使用！ 可能设置的值范围为 1 - 6。查看下面的章节 'Forcing recovery' 以了解这个参数的具体含义。参数设置大于 0 的值代表着 InnoDB 防止用户修改数据的安全度。从 3.23.44 开始，这个参数可用。在 my.cnf 中以数字格式设置。

innodb_fast_shutdown:InnoDB 缺少在关闭之前清空插入缓冲。这个操作可能需要几分钟，在极端的情况下可以需要几个小时。如果这个参数据设置为 1 ，InnoDB 将跳过这个过程而直接关闭。从 3.23.44 和 4.0.1 开始，此参数可用。从 3.23.50 开始，此参数的默认值为 1。

innodb_thread_concurrency:InnoDB 会试图将 InnoDB 服务的使用的操作系统进程小于或等于这里所设定的数值。此参数默认值为 8。如果计算机系统性能较低或 innodb_monitor 显示有很多线程等侍信号，应该将这个值设小一点。如果你的计算机系统有很我的处理器与磁盘系统，则可以将这个值设高一点以充分利用你的系统资源。建议设值为处理器数目+ 磁盘数目。 从 3.23.44 和 4.0.1 开始，此参数可用。在 my.cnf 中以数字格式设置。

以上文章取自http://www.80x86.cn/blog/blogview.asp?logID=1042

在MySQLl裡縮小ibdata1這檔案

在mysql裡縮小ibdata1這檔案

網路上查了一輪...基本上這個ibdata1 (路徑為 /var/lib/mysql/ibdata1),是一個只會長大不會變小的檔案它是mysql的bug.. 在bug還沒解之前,想要縮小有個辦法.

1.導出資料庫數據

2.砍掉重練

3.導入第1步時的數據

細部分解:

1.先備份整個資料庫(怕死)

cd /var/lib/

service mysqld stop

tar -zcvf mysql.tgz mysql

service mysqld start

2.停止服務(也是怕死), 假如db是專給web那麼...

service httpd stop

3.把資料庫的資料導出, 自己有什麼資料自己最清楚, 一個一個拉出來囉

mysqldump --lock-all-tables -u root -p 資料庫名稱 > example.sql

4.導出之後, 會有很多個.sql, 這時提起勇氣...

rm /var/lib/mysql/ibdata1

5.資料庫一個一個導回去

mysql -u root -p 資料庫名稱 <>

6.開啟服務, 仍以web 為例.

service httpd start

7.測試....

成功是一定的,但天有不測風雲, 資料如果不見,至少還有mysql.tgz頂著

MySQL Server 的日常維護中最重要的一項大概就是資料庫的定時備份，而 MySQL 資料庫的備份方式有很多但一般來說大致上可概分為二種：

1. Binary Copy (直接複製資料庫檔案)

2. Dump Database (將資料庫輸出成為文字檔)

一、Binary Copy (直接複製資料庫檔案)

開門見山的說，非常不建議你這樣子做。MySQL 支援許多種不同的 Storage Engine，但並不是每一種 Storage Engine 都是 Binary Portable，意思就是說不是每一種 Storage Engine 都可以讓你把資料庫檔案直接複製到另外一台 MySQL Server，然後還可以正常運作的。MySQL 預設的 MyISAM Storage Engine 是 Binary Portable 的，因此若你的資料庫只有使用 MyISAM Storage Engine 的話，那麼你大可以直接複製資料庫檔案來進行備份（當然還必須考慮到資料的一致性，比方說複製之前先關閉 MySQL Server）。但若您有使用到 InnoDB Storage Engine，那麼你就不能這麼做，因為 InnoDB Storage Engine "不是" Binary Portable，只要 CPU 的浮點運算架構不同，複製過去的資料將無法正常運作。除此之外，你複製起來的 InnoDB 資料庫檔案(share table space)若是還原到不同版本的 MySQL Server 上，也會有很大的機率無法正常運作。除非你很肯定備份(複製)起來的資料只會使用在 "同一台 Server" 並使用 "同一個版本" 的 MySQL Server，不然請勿使用此方法進行備份。

註：

對於 MyISAM Storage Engine，每個資料庫(Database)都是一個獨立的目錄，而資料庫中的資料表則會分別以三個檔案儲存在該目錄中，這三個檔案分別是：

FRM： 儲存這個資料表的結構

MYD： Row Data，也就是你存在資料表(table)裡的資料

MYI： 此資料表的索引

但對於 InnoDB Storage Engine 來說，所有的資料庫(Database)與資料表(Table)都是儲存在同一個(或同一系列)的檔案之中，例如 /var/lib/mysql/ibdata1。

二、Dump Database (將資料庫輸出成為文字檔)

MySQL 在安裝時即有提供一系列的客戶端程式(Client Program)，這些程式其實就是各種功能不同的 Perl Scripts 的集合，其中包括有協助您操控 Server 的 mysqladmin、用來執行 SQL 指令的 mysql、轉換 binary log 用的 mysqlbinlog 等等。其中有一樣工具是專門讓您用來備份資料庫的，那就是 mysqldump。

mysqldump 的使用方式十分的簡易，其語法為：

mysqldump --lock-all-tables -u root -p 資料庫名稱 > example.sql

* --lock-all-tables：進行備份時將正在備份的資料庫裡的資料表，全部鎖定以確保資料的一致性

* -u root：使用 root 帳號進行備份

* -p：需要輸入密碼，如果你的 root 帳號有密碼保護，而你又不加這個選項，就會直接 ACCESS DENIED

* 資料庫名稱：你要備份的資料庫名稱

* example.sql：這個部份你想取什麼名字都行，總之這裡就是備份出來的檔案名稱

開啟備份出來的檔案看看，你會發現裡面其實是由許多 SQL 指令所組成，而這些 SQL 指令就是用來重建整個資料庫用的，因此當您還原資料庫的時候其實對 MySQL 來說，只是單純的重新執行備份檔裡面所有的 SQL 指令。由於備份出來的檔案是單純的文字檔案，因此它是 Binary Portable，你可以將它複製到任何一台 MySQL Server 上然後進行還原。

還原的方式也很簡單，只要使用以下的指令即可：

mysql -u root -p 資料庫名稱 <>

大部份論壇程式都會提供資料庫備份機制來協助您備份論壇的資料庫，但除非您無法直接掌控伺服器(例如租用虛擬主機)或是你不具有使用 SHELL 執行指令的權限，否則不建議您使用那些第三方程式所提供的功能來備份資料庫，不然您有可能會遇到備份出來的東西還原不回去的情況。

mysqlcheck -a -c -o -r -A --user=username --password=pwd -> for check

以上資料取自於 http://crazyrushstar.blogspot.com/2009/05/mysqlibdata1.html

MYSQL- import/export data .dmp file

#import

mysql --host=... --user=... --password=... --databases MySQL_database_name < dump_file_name

eg: in cmd c:\documents and settings mysql -uroot -ppassword -vvf DB01

#export
mysqldump --host=... --user=... --password=... --databases MySQL_database_name > dump_file_name
eg: in cmd c:\documents and settings mysqldump -uroot -ppassword -vvf DB01>db01.dmp

How to change the mysql database location

Identify the database files you wish to migrate into the new data directory

You can use the following command to list the current databases in mysql

mysqlshow -u root -p

Keep this list available as you will reference it in a later step

Shutdown the MySQL database, if it is running mysqladmin -u root -p shutdown

Locate the MySQL configuration file. This is usually located in the directory $OSS_HOME/var/mysql

Note: You should make a backup copy of each file you're editing before modifying the contents

The property you need to change is named datadir. The default value is $OSS_HOME/var/mysql, which places the database files in the same directory as the log files and other runtime generated files. This property is usually located in the [mysqld_safe] section in the configuration file

You need to change:

[mysqld_safe]

datadir = /opt/oss/var/mysql

to

[mysqld_safe]

datadir = /opt/oss/var/mysql/data

Save these changes

Create the directory you specified in the datadir property mkdir -p /optoss/var/mysql/data

Check to make sure the user assigned to execute mysql has read/write privileges on this directory

You may need to modify the directory settings using chown and chmod

Move the databases listed in the first step to the new data directory mv test /opt/oss/var/mysql/data

Note: You can use the copy (cp) command instead if you prefer. Remember to remove the copied files once the migration is complete

You will need to move/copy each database into the new data directory

After all the databases have been migrated to the new data directory, you should start MySQL openpkg rc mysql start

The database files will now be managed under the new data directory. If you encounter any problems during startup, you check the hostname.err file located in the data directory.

以上文章載自http://developer.spikesource.com/wiki/index.php/How_to_change_the_mysql_database_location

MySQL reset root password

service mysqld stop
mysqld_safe --skip-grant-tables &
mysql -u root mysql

UPDATE user SET password=PASSWORD("abcd") WHERE user="root";
FLUSH PRIVILEGES;
exit
service mysqld restart

Howto Remote Root Access to MySql

現在新版的，有一點點不一樣

先從本機登入資料庫
mysql -u root -p
use mysql;

#root
GRANT * ON *.* TO root@'%' identified by '123';
說明如下
GRANT *，這裡的*指的是權限 ，例如 SELECT,INSERT,UPDATE,DELETE 。你也可以只單單賦予 SELECT 權限，例如 GRANT SELECT
ON *.*，這裡的第一個*，是Schema，第二個*是TABLE，例如一個Schema叫project，裏頭有個TABLE叫 USER，所以這裡就可以寫成 ON project.user
root@'%'，root就是你要給予的帳號，%是指定的IP區段，例如192.168.0.1，192.168.0.%, 192.168.%.%, 192.%,%,%, 127.0.0.1, localhostu 依此類推
'123'，則是密碼

更新權限表，這個指令沒有執行，前面就白做了。
FLUSH PRIVILEGES;


-------------------- 我是分隔線 ----------------------------
舊的版本方式

1.set root password.

mysql -u root

設定 root
mysql> SET PASSWORD FOR 'root'@'LOCALHOST' = PASSWORD('123');
mysql> SET PASSWORD FOR 'root'@'192.168.0.1' = PASSWORD('234');
mysql> SET PASSWORD FOR 'root'@'192.1680.100' = PASSWORD('345');

以上，從不同地方的IP登入，密碼就會是不同,請注意!!


設定 root 可以從任何地方來登入

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123123' WITH GRANT OPTION;

*.* 表示 schema.object 如 company.webuser

'%' 表示任何地方



mysql> exit

We’re almost done now. We just have to tell Mysql to allow remote logins.

vi /etc/my.cnf

DB 資料存放處所
datadir=/var/lib/mysql