fr33.info

User Tools

Site Tools

linux:databases:mysql

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
Last revision Both sides next revision
linux:databases:mysql [2017/03/03 14:24]
tkilla 		linux:databases:mysql [2017/11/22 01:09]
tkilla
Line 80: Line 80:
  
    mysqldump --opt --host=127.0.0.1 --default-character-set=utf8 --user=root --password=${PWD} $DB > $FILE    mysqldump --opt --host=127.0.0.1 --default-character-set=utf8 --user=root --password=${PWD} $DB > $FILE
 +
 +
 +If you get "error 34 too many open files".. increase open_files_limit in /etc/mysql/my.cnf
 +
 +  open_files_limit = 50000
 +
 +
 +Restart mysql and check to see current setting:
 +  SHOW VARIABLES LIKE 'open%';
 +
 +
 +
 +interesting parameters for replication:
 +
 +--lock-tables - READ LOCK (no writes) for currently dumped database: ensures consitent dump
 +                instead of
 +--single-transaction   Use this setting with extreme caution when backing non-transactional tables
 +                BUT
 +will give you an exact point-in-time snapshot of the data
 +--single-transaction produces a checkpoint that allows the dump to capture all data prior to the
 +checkpoint while receiving incoming changes.
 +Those incoming changes do not become part of the dump. That ensures the same point-in-time for all tables.
 +USE IF ALL TABLES ARE INNODB
 +
 +--opt includes --lock-tables
  
 ---- ----
Line 88: Line 113:
  
    mysql --host=127.0.0.1  -u root --port=$PORT --password=${DBPWD} ${DBNAME} < ${MYSQLDUMP_FILE}    mysql --host=127.0.0.1  -u root --port=$PORT --password=${DBPWD} ${DBNAME} < ${MYSQLDUMP_FILE}
 +
  
 ---- ----
Line 152: Line 178:
  
 -- --
 +
 +== Convert MyISM to INNODB  ==
 +
 +.. because it's better :)
 +
 +a little script to get all required alter table statements 
 + 
 +  mysql -u root --password=xxxxx -e "SELECT  CONCAT('USE ', table_schema, ';  ALTER TABLE ', table_name, ' ENGINE=InnoDB;') FROM  information_schema.tables WHERE  engine = 'MyISAM' AND  table_schema NOT IN ('mysql', 'information_schema', 'performance_schema');" > /root/alter_to_innodb.sql
 +
 +then execute the file via mysql command: mysql -u ... < /root/alter_to_innodb.sql
 +
 +read this great article: http://mysql.rjweb.org/doc.php/myisam2innodb
 +
  
 == SSL Connection  == == SSL Connection  ==
linux/databases/mysql.txt ยท Last modified: 2017/11/22 01:10 by tkilla

Page Tools

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki