Tag Info

Hot answers tagged

2

Well, I figured out that if you use the backticks ("`") around the table name (like mysqldump uses in the comment-separator sections, e.g. `call`), it works as expected. So, the above should look like: -- -- Table structure for table `call` -- DROP TABLE IF EXISTS `call`; CREATE TABLE `call` ( path varchar(255) NOT NULL default '', call_num int(12) ...


1

If your big tables are innodb, you can use --single-transaction to not lock the table. Other possibility is Percona XtraBackup Or pt-archiver (again Percona)


1

You can use prepare statement for this and also need a delimiter for the procedure something as delimiter // create procedure table_create (in tablename varchar(100)) begin set @table_ = concat(tablename, '_meta'); set @qry = concat("create table ",@table_,"(name varchar(50), value varchar(50)) ENGINE=MyISAM;"); prepare stmt from @qry; execute stmt; ...


1

Are you using MyISAM? Or InnoDB? Or both? [!!] Query cache is disabled This is "good", not "bad". [!!] Table cache hit rate: 0% (400 open / 78K opened) How many tables to you have? Thousands? Please elaborate on why so many. (This is usually a design flaw.) Meanwhile, increase table_open_cache to, say, 2000. What MySQL version are you ...


1

Well, this is a bit of voodoo/shotgun debugging, but I've got it functioning okay for the time being. I set the extended_keys option to on, and also created a covering index on the 8-million-row table in question. Now I'm getting an execution plan that's even better than the acceptable plan the old server was coming up with (which was only using Magento's ...


1

since the the following command use my_database; executed too fast when you try this locally (on the same server), and take a log time when you executed remotely, there is some common causes for that like the connection between the server and client is slow, or there is a delay (lag) on attempting to retrieve and verify reverse DNS, you can try to add the ...


1

SUGGESTION #1 Since you are having issues with swap and looking at your current InnoDB settings, I would suggest one of the following settings: innodb_buffer_pool_instances=2 # If your Server is DualCore innodb_buffer_pool_instances=4 # If your Server is QuadCore innodb_buffer_pool_instances=6 # If your Server is HexaCore innodb_buffer_pool_size=90G I ...


1

I don't have enough reputation to comment on the previous answers. So I thought I'd elaborate a bit. 1) ON DELETE CASCADE means if the parent record is deleted, then any referencing child records are also deleted. ON UPDATE defaults to RESTRICT, which means the UPDATE on the parent record will fail. 2) ON DELETE action defaults to RESTRICT, which means ...


1

You must understand what the binary logs are: they store all the queries (in STATEMENT format) or row changes (in ROW format) that happened since they were recorded. So, your command: mysqlbinlog -d dbname --start-position=107 mysql-bin.000{727..755} | mysql dbname basically executes all insert, updates & deletes that occurred to the server during ...



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