MySQL のバックアップ(No.7)

[ トップ ]   [ 新規 | 一覧 | 単語検索 | 最終更新 | ヘルプ | ログイン ]

• バックアップ一覧
• 差分 を表示
• 現在との差分 を表示
• ソース を表示
• MySQL へ行く。
  • 1 (2008-06-08 (日) 02:56:31)
  • 2 (2008-08-01 (金) 15:18:07)
  • 3 (2008-08-12 (火) 16:20:04)
  • 4 (2008-10-15 (水) 17:03:28)
  • 5 (2008-10-20 (月) 10:41:43)
  • 6 (2008-10-24 (金) 13:46:44)
  • 7 (2008-12-17 (水) 15:09:36)
  • 8 (2008-12-22 (月) 01:41:06)
  • 9 (2009-01-07 (水) 16:12:08)
  • 10 (2009-01-27 (火) 19:26:17)
  • 11 (2009-03-23 (月) 07:22:42)

---

MySQL

rpm -qa | grep mysql

rpm -qa | grep mysql| xargs rpm -e

http://dev.mysql.com/downloads/

rpm -Uvh --nodeps MySQL-server-4.1.7-0.i386.rpm
rpm -Uvh --nodeps MySQL-client-4.1.7-0.i386.rpm
rpm -Uvh --nodeps MySQL-devel-4.1.7-0.i386.rpm

http://dev.mysql.com/downloads/mysql/4.1.html#Source

./configure --prefix=/usr/local/mysql --with-mysqld-user=mysql
--with-charset=sjis --with-extra-charsets=all --with-berkeley-db
--with-innodb --without-readline --with-named-thread-libs="-lpthread"

configure: error:This is a linux system and LinuxThreads was not found.

make

mysqld.o: In function `main':
mysqld.cc:(.text+0x3ee0): undefined reference to `my_fast_mutexattr'
mysqld.o:mysqld.cc:(.text+0x4143): more undefined references to `my_fast_mutexattr' follow
collect2: ld returned 1 exit status

http://bbs.fedora.jp/read.php?FID=9&TID=3606

#include <assert.h>

#ifdef PTHREAD_ADAPTIVE_MUTEX_INITIALIZER_NP
pthread_mutexattr_t my_fast_mutexattr;
#endif

make install

mysql-server.sh

basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
if test "$datadir" != "/usr/local/mysql/var"
pid_file=$datadir/`/bin/hostname`.pid

RedHat?

cd support-files
cp mysql.server.sh /etc/rc.d/init.d/mysqld
chmod 755 /etc/rc.d/init.d/mysqld 
chkconfig --add mysqld 
chkconfig --level 345 mysqld on

cd support-files
cp mysql.server.sh /usr/local/etc/rc.d/
chmod 755 /usr/local/etc/rc.d/mysql.server.sh

http://www.cpan.org/modules/by-module/DBD/

perl Makefile.PL
make
make test
make install

failed: Can't load '/root/DBD-mysql-4.005/blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.12

cd /usr/local/lib
ln -s /usr/local/mysql/lib/mysql/libmysqlclient.so.12 libmysqlclient.so.12

./configure --with-mysql
make
make install

groupadd mysql
useradd -g mysql -d /dev/null -s /bin/false mysql

PHP

scripts/mysql_install_db

chown -R mysql:mysql /usr/local/mysql/var

mysqld_safe --user=mysql &
/usr/local/mysql/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql/bin/mysqladmin -u root -h localhost password 'new-password'

mysql -u root -p

http://www.nihon-eng.co.jp/c-break/TechNote/mysql/MySQL5_Inst.htm

http://web.kuicr.kyoto-u.ac.jp/manual/mysql/manual.ja_Access_denied.html

http://dev.mysql.com/downloads/mysql/5.0.html

wget http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.13-rc.tar.gz/from/http://www.softagency.co.jp/MySQL/
tar xzvf mysql-5.0.13-rc.tar.gz
cd mysql-5.0.13-rc

./configure --help
./configure --prefix=/usr/local/mysql5 --with-mysqld-user=mysql
--with-charset=binary --with-extra-charsets=all --with-berkeley-db
make
make install

groupadd mysql
useradd -g mysql -d /dev/null -s /bin/false mysql

cp support-files/my-large.cnf /etc/my.cnf

[client]
 default-character-set=ujis
[mysqld]
 default-character-set = ujis
 skip-character-set-client-handshake
[mysqldump]
 default-character-set = ujis
[mysql]
 default-character-set = ujis

scripts/mysql_install_db
chown -R mysql:mysql /usr/local/mysql5/var/

/usr/local/mysql5/bin/mysqld_safe --user=mysql &
/usr/local/mysql5/bin/mysqladmin -u root password 'new-password'
/usr/local/mysql5/bin/mysqladmin -u root -pnew-password  -h localhost.localdomain password 'new-password'

http://wota.jp/ac/?date=20061011#c15 RedHat? http://dev.mysql.com/downloads/gui-tools/5.0.html

[mysqld]
old-passwords

http://www.mysql.gr.jp/frame/modules/bwiki/?Contrib#content_1_2

./mysql_change_char_len.sh --fix --user=hogeuser --password=hogepass -h127.0.0.1 -P3307

http://private.ceek.jp/archives/001772.html
http://www.klab.org/media/mysql/
http://tech.feedforce.jp/mysql_40_to_50.html

cp /usr/local/mysql/share/mysql/mysql.server /etc/init.d/mysqld

chmod 755 /etc/init.d/mysqld
chkconfig --add mysqld

mysql -u root -p rootpassword
mysql -uroot -prootpassword
mysql -user=root --password=rootpassword

• mysqld_safe --user=mysql &

• mysqladmin shutdown
  mysqladmin shutdown -uroot -phogepass

• mysqladmin ping 

• mysqladmin version
  mysqladmin version -uroot -phogepass

• mysql -u root --password=passwd database̾

  /usr/local/mysql5/bin/mysql -h127.0.0.1 -P3307 -uroot -phogepass

  /usr/local/mysql5/bin/mysql -P3307 -uroot -phogepass

• mysqladmin -uroot -phoge variables

• USE hogedb;

• SHOW DATABASES;

• show variables;

• SHOW PROCESSLIST;

  watch -n1 'mysql -h192.168.1.1 -P3306 -uhogeuser -phogepass -e"show processlist"'

• SHOW [FULL] PROCESSLIST

• SHOW TABLES;

• SHOW GRANTS FOR hoge@'localhost';

• SHOW STATUS [LILE pattern]

• SHOW TABLE STATUS

SQL

• grant create,drop,select,insert,update,delete on *.* to test@localhost identified by 'passwd';

  grant all privileges on *.* to user@'192.168.1.0/255.255.255.0' identified by 'passwd';

• delete from mysql.user where User='a';

• SET PASSWORD FOR root=PASSWORD('hoge');

  SET PASSWORD FOR root@localhost=PASSWORD('hoge');

• select User,Host from mysql.user;

• create database aaa;

  create database aaa character set sjis;

• drop database aaa;

• ALTER TABLE hogetable ADD COLUMN hogekoumoku VARCHAR(100) NOT NULL;

• ALTER TABLE hogetable DROP hogekoumoku;

• ALTER TABLE hogetable MODIFY COLUMN hogekoumoku VARCHAR(30);

my.cnf

• key_buffer=256M
  
• table_cache=256
  
• max_allowed_packet=1M
  
• sort_buffer=1M
  
• record_buffer=1M
  
• long_query_time=1
  
• join_buffer = 131072
  

ODBC

http://dev.mysql.com/

JDBC

http://dev.mysql.com/downloads/

org.gjt.mm.mysql.Driver

jdbc:mysql://hogeserver/hogedb?useUnicode=true&characterEncoding=SJIS

jdbc:mysql://hogeserver/hogedb?useUnicode=true&characterEncoding=SJIS

jdbc:mysql://hogeserver/hogedb?useUnicode=true&characterEncoding=MS932

SetCharacterEncodingFilter?

<filter>
  <filter-name>Set Character Encoding</filter-name>
  <filter-class>filters.SetCharacterEncodingFilter</filter-class>
   <init-param>
     <param-name>encoding</param-name>
     <param-value>MS932</param-value>
   </init-param>
 </filter>
 <filter-mapping>
   <filter-name>Set Character Encoding</filter-name>
   <url-pattern>/*</url-pattern>
</filter-mapping>

Oracle

MySQL Control Center

http://dev.mysql.com/downloads/other/mysqlcc.html

mysqldump -F -l hogedb > dump.sql

mysql hogedb < dump.sql

mytop

http://jeremy.zawodny.com/mysql/mytop/

tar xzvf mytop-1.4.tar.gz
perl Makefile.PL

Warning: prerequisite Term::ReadKey 2.1 not found.

CPAN

perl -MCPAN -e shell
install Term::ReadKey

perl Makefile.PL
make
make install

mytop -uhogeuser -phogepass -ddatabase -s1

http://jeremy.zawodny.com/mysql/mytop/mytop.html

table,type,possible_key,key,key_len,ref,rows,Extra

RESET MASTER;

PURGE MASTER LOGS TO 'machine-bin.005';

SHOW MASTER LOGS;

PURGE MASTER LOGS BEFORE '2005-01-01 01:01:01';

log-bin         = /usr/local/mysql/binlog/binlog

http://www.softagency.co.jp/mysql/Manual/4.1.1/html-split/manual_4.html#SEC314

ib_logfile0
ib_logfile1
ibdata1

TOOL

MySQL GUI Tools

TIPS

mysqlhotcopy -u root -p hoge dbmoto dbsaki

ERROR 1251: Client does not support authentication protocol requested by server; consider upgrading MySQL client

http://dev.mysql.com/doc/mysql/ja/Old_client.html

mysql> UPDATE user SET Password = OLD_PASSWORD('mypass')
   -> WHERE Host = 'some_host' AND User = 'some_user';

SET PASSWORD FOR hoge@localhost = OLD_PASSWORD('hogepass');

mysql> FLUSH PRIVILEGES;

[mysqld]
old-passwords

mysql -u root -pyourpassword database < hoge.sql

optimize table hogetable;

[mysqld]
default-character-set=sjis
[mysql]
default-character-set=sjis

1030: Got error 127 from table handler

repair table hogetbl;

check table hogetbl;

safe_mysqld --log=/var/log/mysql

koumoku1 + interval "1" DAY

koumoku1 + interval "1 5" DAY_HOUR

java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00'

select date_format(datekoumoku,'%Y-%m') ,count(*) from table1 where group by date_format(datekoumoku,'%Y-%m')

http://www.mysql.gr.jp/ http://dev.mysql.com/doc/mysql/ja/Upgrading-from-4.0.html
http://www.atmarkit.co.jp/flinux/rensai/mysql01/mysql01b.html
http://www.y-kit.jp/saba/xp/mysqllinuxinst.htm
http://digibot.jp/tips/linux/004.html
http://perldoc.jp/docs/modules/DBD-mysql-2.1026/DBD/mysql/INSTALL.pod
http://tsuttayo.sytes.net/mysql/

phpMyAdmin

• #comment

     

Site admin: anonymous

PukiWiki 1.5.1 © 2001-2016 PukiWiki Development Team. Powered by PHP 5.6.40. HTML convert time: 0.041 sec.