Tag Info

Hot answers tagged

7

It's not difficult. Just need a set section in your LOAD DATA INFILE command. mysql> create table tst ( datecol datetime ); Query OK, 0 rows affected (0.04 sec) mysql> File: [root@node1 ~]# cat /tmp/tst.dat 01-JAN-03 21-MAR-09 28-FEB-11 [root@node1 ~]# Test: mysql> load data infile '/tmp/tst.dat' -> into table tst -> fields ...


2

The most common cause of this is virus scanning, as virus scanners tend to lock files. Try excluding the directory from scanning.


2

memlock was the issue. turns out, memlock settings were at default (see above), and this might have been preventing the allocation of 4+gb of memory to mysql. changed memlock settings in '/etc/security/limits.conf', and limit to 8GB. then, allocated 7GB to mysql buffer pool. worked. mysql now starts and stops without throwing any errors in the log. a few ...


2

By all means, remove them. Those are just temporary tables mysqld had written to disk. In all likelihood, mysqld probably crashed when those temp tables were made. Look at the timestamps: Max is 2012-10-09 Min is 2012-08-21 The sum of these files are over 1G. They are just taking up room. I know they are temp tables because MySQL uses MyISAM as the ...


1

The reason for the discrepancy is obvious. When you run SELECT A.*,B.* FROM (SELECT VERSION() MySQLVersion) A, (SELECT COUNT(1) MySQLProcColumns FROM information_schema.columns WHERE table_schema='mysql' AND table_name='proc') B; you get mysql> SELECT A.*,B.* FROM (SELECT VERSION() MySQLVersion) A, -> (SELECT COUNT(1) MySQLProcColumns FROM ...


1

Step 01 : Run this script to Dump Everything from MySQL 5.0 cd /root MYSQL_CONN="-uroot -p..." SQLSTMT="SELECT schema_name FROM information_schema.schemata WHERE" SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN" SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')" MYSQL_OPTIONS="--skip-column-names -A" mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} ...


1

You will want to use mysqld_multi, which is part of a typical MySQL server install. On my Ubuntu server, it was located at /usr/bin/mysqld_multi. MySQL's Documentation on mysqld_multi Here is a how to, I used to set up mysqld_multi: MySQL Multi How to Update, running a 2nd instance from the shell Start another mysqld using a different, port, socket and ...


1

The least disruptive method would be to use the general query log. The query log output can be a database table or a log (text) file; however it does not support filtering by DML operation or database so all SELECT statements on all databases will be logged as well. Obviously you can filter the logging output later. Also note: The session sql_log_off ...


1

If you will recreate all your views this way (and will create all new ones with this syntax): CREATE SQL SECURITY INVOKER VIEW applications AS SELECT id, product_id FROM modules WHERE item_type='application'; you will only need to make sure a user on another instance where you restored your database has grants to EXECUTE on your views and ...


1

You missed three(3) things On DB1, mysql -uroot -ppassword -e"SET GLOBAL innodb_fast_shutdown = 0" On DB1, service mysql stop (DB1) Copying the backup (/var/lib/mysql) on DB1 Copy /etc/my.cnf on DB1 to /etc folder on DB2 Importing DB1 backup to DB2 (/var/lib/mysql) On DB2, chown -R mysql:mysql /var/lib/mysql Start mysql on DB2 Step 1 flushes everything ...


1

I actually wrote about this back on August 27, 2012 : Proper tuning for 30GB InnoDB table on server with 48GB RAM. Here is an excerpt from my past answer (under the section entitled Log File Size): EXCERPT 5MB is the default size for innodb_log_file_size. Percona's mysqperformanceblog.com gave two good articles on computing the right size for your ...


1

Have ended up Querying information_schema.Table_Privileges and scripting Grants & Revokes based on the result.


1

MySQL 5.6 introduces Global Transaction Identifiers to replication, and complements these with new failover and switchover utilities which can automate fault recovery You can learn more about replication in MySQL 5.6 here: http://dev.mysql.com/tech-resources/articles/mysql-5.6-replication.html You can also watch a tutorial demonstrating how these utilities ...


1

Believe it or not, I just answered a question like this 5 days ago: MySQL Replication and High Availability From that answer I provided, I advise against automatic failover with pure MySQL Replication unless your specifically script it to check for replication lag or totally unavailability of the old master (STONITH) As an alternative, just setup a DRBD ...



Only top voted, non community-wiki answers of a minimum length are eligible