Linux?
mysqldump --opt --all-databases --events --default-character-set=binary -u root --password=password > /backupDirectory/mysqlDump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[mysqldump] user=username password=password
mysqldump --defaults-extra-file=/hogehoge/mysqlDump.conf --opt --all-databases --events --default-character-set=binary > /backupDirectory/mysqlDump.sql
[root@centos ~]# vi mysql-backup.sh作成 #!/bin/bash PATH=$PATH:/usr/local/sbin:/usr/bin:/bin # バックアップ先ディレクトリ BACKDIR=/backup/mysql # MySQLrootパスワード ROOTPASS=xxxxxxxx # バックアップ先ディレクトリ再作成 rm -rf $BACKDIR mkdir -p $BACKDIR # データベース名取得 DBLIST=`ls -p /var/lib/mysql | grep / | tr -d /` # データベースごとにバックアップ for dbname in $DBLIST do table_count=`mysql -u root -p $ROOTPASS -B -e "show tables" $dbname|wc -l` [ $table_count -ne 0 ] && mysqlhotcopy $dbname -u root -p $ROOTPASS $BACKDIR | logger -t mysqlhotcopy done [root@centos ~]# chmod 700 mysql-backup.sh
mysqldump -u USER_NAME -p -h HOST_NAME DB_NAME > OUTPUT_FILE_NAME
mysqldump -u USER_NAME -p -h HOST_NAME DB_NAME TABLE_NAME > OUTPUT_FILE_NAME
mysql -u username -ppassword -D DBname < dumpfilename.sql
SELECT * FROM テーブル名 INTO OUTFILE "ファイル名" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '囲み文字の指定';
select column1,count(column2) as column2_num from hoge_table group by column2_num having count(column2) = 1
create table testm ( key1 char(008) primary key, data1 int8, data2 int8, data3 int8 ) type=InnoDB;
CREATE TABLE book2 ( id INT(11) NOT NULL AUTO_INCREMENT, title VARCHAR(64), author_name VARCHAR(32), detail TEXT, image VARCHAR(64), PRIMARY KEY (id) );
CREATE TABLE book2 ( id INT(11) NOT NULL AUTO_INCREMENT, title VARCHAR(64), author_name VARCHAR(32), detail TEXT, image VARCHAR(64), PRIMARY KEY (id) ) type=InnoDB DEFAULT CHARSET=utf8;
alter table personal add new_col_name varchar(20) after col_name;
alter table goods modify no smallint;
insert into table1 (colum1,colum2,colum3) select table2.colum1,table2.colum2,table2.colum3 from table2 where not exists (select colum2 from table1 where table2.colum1=table1.colum1 and table2.colum2=table1.colum2 and table2.colum3=table1.colum3)
insert into table1 (colum1,colum2,colum3) select table2.colum1,table2.colum2,table2.colum3 from table2 left join table on table2.colum1=table1.colum1 and table2.colum2=table1.colum2 and table2.colum3=table1.colum3 where table1.colum1 is null;
ALTER TABLE table1 ADD INDEX index_name(colum1, colum2, colum3); ALTER TABLE table2 ADD INDEX index_name(colum1, colum2, colum3);
mysql --version
SHOW GLOBAL VARIABLES
SHOW GLOBAL STATUS
SHOW PROCESSLIST SHOW FULL PROCESSLIST; ← Info が長い文字列の場合に省略しない
pager less -S
nopager
CREATE USER 'hoge'@'sampleHost' IDENTIFIED BY 'password';
DROP USER 'hoge'@'sampleHost';
GRANT ALL ON *.* TO user; GRANT SELECT, INSERT ON *.* TO user;
GRANT ALL ON db_name.* TO user; GRANT SELECT, INSERT ON dbName.* TO user;
GRANT ALL ON db_name.table_name TO user; GRANT SELECT, INSERT ON db_name.mytbl TO user;
GRANT SELECT (col1), INSERT (col1, col2) ON db_name.table_name TO user;
show grants for 'ユーザー名'@'ホスト名';
check table `【テーブル名】`;
check table tableName;
+-----------+-------+----------+----------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-----------+-------+----------+----------------------------------------------+ | tableName | check | warning | Table is marked as crashed | | tableName | check | error | Size of datafile is: 0 Should be: 84 | | tableName | check | error | Corrupt | +-----------+-------+----------+----------------------------------------------+
repair table `【テーブル名】`;
repair table tableName;
check table `【テーブル名】`;
tableName
+-----------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-----------+--------+----------+----------+ | tableName | repair | status | OK | +-----------+--------+----------+----------+
sudo yum remove mariadb-libs sudo rm -rf /var/lib/mysql
yum install http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
sudo yum install mysql-community-server mysql-community-client
yum install --enablerepo=remi,remi-php70 php-mysqlnd
sudo systemctl start mysqld
sudo cat /var/log/mysqld.log | grep password
mysql_secure_installation
sudo systemctl restart mysqld
sudo systemctl enable mysqld
sudo systemctl status mysqld
mysql_upgrade -u root -p