分类 ‘MySQL’ 归档文章

MySQL 多数据库的备份

本文介绍了用 –defaults-group-suffix 参数,传递写在指定配置文件中的所有的数据库配置到 mysqldump 命令来同时备份多个库的实现办法。

$ mysqldump  –defaults-group-suffix=_usera –defaults-file=./.my.cnf  usera_a > user_a.sql

./.my.cnf  的格式如下:

[client]
port=3306
password=mypassword
# 脚本中要求 DB 名须和 user 名一致

[client_usera]

# 等号前后有空格隔开

host = hosta
user = usera

[client_userb]
host = hostb
user = userb

具体脚本如下:

(更多…)

Convert back WP MU to WordPress

Godaddy’s WordPress only support single user, not the MU version, have to convert back original WPMU version to single user version.

Since I have to install multiple version of WordPress, each of them has its own DB. I put a .sid for each of mysql DBs as original .my.cnf file.

Contents like this:

# freelamp.com
[client]
host = frexxxxxxx.db.7114699.hostedresource.com
user = frexxxxx
port=3306

[mysql]
database = frexxxxxxxx

Then create alias in .bash_profile for each mysql instance:

alias mysql.albertxu=’mysql  –defaults-file=~/.albertxu -p ‘
alias mysql.freelamp=’mysql  –defaults-file=~/.freelamp -p ‘

So that I can just use mysql.freelamp to log into DB.

The first step is to import the original exported wpmu DB, it includes all the other users tables(blogs), we’ll remove them later.

I write a rename.sh, to rename the required tables:

-bash-3.2$ cat rename.sh
#!/bin/bash
id=$1
[ -z “$id” ] && echo “Syntax: $0 blog_id” && exit 0
FILE=$id.sql
echo > $FILE

# we ignore this two tables: cas_count cas_image
for t in  posts commentmeta comments links options postmeta terms term_relationships term_taxonomy
do
echo “DROP TABLE IF EXISTS wp_$t ;” >> $FILE
echo ALTER TABLE wp_”$id”_$t RENAME TO wp_$t “;” >> $FILE
done

echo UPDATE wp_options set option_name=\”wp_user_roles\” WHERE option_name=\”wp_”$id”_user_roles\” “;” >> $FILE
echo UPDATE wp_usermeta set meta_key=\”wp_capabilities\” WHERE meta_key=\”wp_”$id”_capabilities\” “;” >> $FILE
echo UPDATE wp_usermeta set meta_key=\”wp_user_level\” WHERE meta_key=\”wp_”$id”_user_level\” “;” >> $FILE

cat $FILE

Check the output of the above script, then run mysql.freelamp < 2.sql ( in the example, $id = 2 ), you should see all the blog contents in original WPMU site now migrated. The remain thing is log in the control panel to change “Media” folder path and PermsLinks Settings.

And remove non-used users also.

At last, you should remove all these not in using other user’s tables.

SQL command: show tables like “wp\__\_%”  can list all the others tables not needed, just filter them to a script and make it like

DROP TABLE IF EXISTS $tablename.

All set.   LOL.

MariaDB

MySQL
Image via Wikipedia

Oracle 收购 Sun 已经成为事实,悲惨的是 MySQL 的下场。

看到了 MariaDB 的存在,或许是另外一个契机

Reblog this post [with Zemanta]

Use DRBD to Provide Rock-Solid MySQL Redundancy

MySQL ndb 引擎的 cluster 配置,在读写方式下并不能达到普通 InnoDB 的性能。用 DRBD 来实现? MySQL 的高可靠性未尝不是一件有益的尝试。

Maximizing Sysbench OLTP performance for MySQL

MySQL 的优化是一个永恒的话题,这篇博客介绍了作者用 sysbench 测试结果,并提供的最佳参数:

  1. Use multiple sysbench threads via the --num-threads parameter
  2. Set innodb_thread_concurrency = 0 in my.cnf
  3. Set innodb_buffer_size equal to or greater than the table size

12 个比较有用的 MySQL 配置参数

不错的一个文章,有时间慢慢翻译。

  • Altering Index Buffer Size (key_buffer)

This variable controls the size of the buffer used when handling table indices (both read and write operations). The MySQL manual recommends that this variable be increased to as much as you can afford to ensure you get the best performance on indexed tables, and recommends a value equivalent to about 25 percent of the total system memory. This is one of the more important MySQL configuration variables and if you’re interested in optimizing and improving performance, trying different values for the key_buffer_size variable is a good place to start.

  • Altering Table Buffer Size (read_buffer_size)

When a query requires a table to be scanned sequentially, MySQL allocates a memory buffer to this query. The read_buffer_size variable controls the size of this buffer. If you find that sequential scans are proceeding slowly, you can improve performance by increasing this value, and hence the size of the memory buffer.

  • Setting the Number of Maximum Open Tables (table_cache)

This variable controls the maximum number of tables MySQL can have open at any one time, and thus controls the server’s ability to respond to incoming requests. This variable is closely related to the max_connections variables – increasing this value allows MySQL to keep a larger number of tables open, just as increasing max_connections increases the number of allowed connections. Consider altering this value if you have a high-volume server which receives queries on multiple different databases and tables.

  • Activating the Query Cache (query_cache_type)

MySQL 4.x includes a query cache which can improve query response time by caching the results of frequently-used queries and returning the cached data on subsequent calls to the same query. Whether or not the query cache is used is controlled by the query_cache_type variable, which may be set to ON, OFF or DEMAND. It’s generally a good idea to turn this on, especially if you have large numbers of identical SELECT queries being executed repeatedly on the server.
Tip: You can also use the query_cache_size variable to control the amount of memory allocated to the MySQL query cache. Increase this value for high-volume servers.

  • Setting the Maximum Size Of A Join (max_join_size)

This variable sets the maximum number of rows that a SELECT query should scan when performing a table join. This is useful to catch badly-written queries that might end up scanning millions of rows, thus decreasing the server’s ability to satisfy other requests. Because this variable impacts all queries and users, it should be set only after careful consideration of what constitutes a legal query on your specific system.

  • Setting the Maximum Number of Permitted Connections (max_connections)

This variable controls the maximum number of incoming client connections MySQL can deal with at any one time. If your application is likely to experience large numbers of independent client connections simultaneously, it’s a good idea to increase this value to avoid the “Too many connections” error.

  • Deciding a Time Limit for Long Queries (long_query_time)

MySQL comes with a so-called slow query log, which automatically logs all queries that do not end within a particular time limit. This log is useful to track inefficient or misbehaving queries, and to find targets for optimization algorithms. The long_query_time variable controls this maximum time limit, in seconds.
Tip: To avoid false positives, set this to a relatively higher value on systems that experience heavy load.

  • Altering the Transaction Isolation Level (tx_isolation)

This variable controls the transaction isolation level, or the extent to which concurrent transactions and the changes they make to a table are “visible” to each other. There are four transaction levels in an ACID-compliant transactional RDBMS like MySQL, and you can set MySQL to use any one of them with this variable.
Note: It’s important to remember that this variable controls the global, default transaction level. Individual clients can, of course, override this value on a per-transaction basis if needed.

  • Activating the Binary Log (log_bin)

MySQL’s binary log keeps track of all queries that alter the data in the database. It’s mostly used to efficiently and accurately perform replication operations, and to restore the system to a stable snapshot in the event of a failure or transaction interruption. The log_bin variable enables this log.

  • Auto-Executing SQL Code on Client Connection (init_connect)

This variable can be used to run SQL commands on the server for every client that successfully opens a connection. This variable is usually set to one or more SQL commands, which are executed by the server to perform client-specific initialization.

  • Setting a Timeout for Interactive Connections (interactive_timeout)

This is a particularly useful variable to set if your MySQL server receives numerous requests for interactive use. This variable controls how long the server should wait for activity on an interactive client connection before terminating the connection. Set this to a reasonable value—five minutes is usually fair, although you will want to reduce that if your server has a lot of clients requesting interactive connection.
Tip: You can use the connect_timeout variable to control how long MySQL waits for a client connection to be consummated before terminating it with an error.

  • Setting the Time Zone (system_time_zone)

This variable controls the time zone MySQL uses for all its date/time bookkeeping. It’s important to ensure that this variable is correctly set, as using the wrong time zone can affect the accuracy of values entered into DATE, TIME and TIMESTAMP fields, as well as flaw date arithmetic operations.

MySQL Tuner

这个小小的脚本能发现 MySQL 配置文件的一些问题,很是有用。

输入用户名和密码后,它会给你提供一个诊断结果。
[root@freelamp ~]# mysqltuner.pl

>>? MySQLTuner 0.9.9 – Major Hayden
>>? Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>? Run with ‘–help’ for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

(更多…)

RHEL5 上的 mod_auth_mysql

以前写过关于 mod_auth_mysql 的文章,新系统上马了 Cacti 和 Awstats 想到用认证机制保护起来,就想到了那篇文章。但是去 SourceForge 下载的 3.0.0 版本在 RHEL5 上安装时,报错: (更多…)

低版本 Client 连接 MySQL 4.1 以上版本的口令问题

由 徐永久 发表于 2007年08月20日 21:35。

一般都用 tarball 编译的方式来安装 LAMP。一个 RHES 4.1 的 Red Hat 上安装的 MySQL 不完整,删除老的 4.1.20 版本后,安装了 MySQL 网站上的

MySQL-server-standard-4.1.22-0.rhel4.i386.rpm
MySQL-client-standard-4.1.22-0.rhel4.i386.rpm
MySQL-devel-standard-4.1.22-0.rhel4.i386.rpm

MySQL 工作了, 可是原来的 php-mysql 却不支持 PHP 了。
只好通过 rpmfind , 下载
(更多…)

对拥有一个几十万行表的 MySQL 性能优化的简单办法

由 徐永久 发表于 2005年05月12日 00:30。

数据库的优化大概是在系统管理中最具有挑战性的了,因为其对人员的素质要求几乎是全方面的,好的 DBA 需要各种综合素质。在排除了操作系统,应用等引起的性能问题以外,优化数据库最核心的实际上就是配置参数的调整。本文通过一个简单的参数调整,实现了对拥有一个几十万行表的 group by 优化的例子。通过这个简单的调整,数据库性能有了突飞猛进的提升。
本例子是针对 MySQL 调整的,不像其他商业数据库,MySQL 没有视图,特别是 Oracle 可以利用固化视图来提升查询性能,没有存储过程,因此性能的调整几乎只能通过配置合适的参数来实现。
(更多…)

Google+