Archive for the ‘Mysql’ Category
Conversion table types in MySQL #2
Sometimes it is necessary to convert a MySQL table in an existing database. For example, I ran into this with the need to create relationships between tables through the foreign key. As it turned out, the default MySQL MyISAM engine does not support this. To create a link table must be of type InnoDB. But this is not a big problem, because you can convert the table. To convert a single table SQL query is enough (which can be done, for example, through phpmyadmin):
Convert MyISAM in InnoDB for WordPress
Recently I migrated all MyISAM tables to InnoDB for some WP sites of clients.
I saw some improvement in mysql performance, specially when editing posts. I think earlier parallel write on
1 | wp_posts |
table were getting blocked because MyISAM do not support row-level locking.
Optimal size of innodb_log_file_size
As you know, during commit InnoDB writes the data at once into data files and records changes in the first innodb_log_file. The fact that write data directly to the table – a much more expensive operation than to record changes in the binary log.
Keeping innodb_log_file allows optimization of I/O: write data to successive large pieces, and more quickly serve customers (customer commits quickly made, and the data in the table space are recorded in the background). Therefore, the larger the file, the more opportunities to optimize InnoDB I/O. Currently, the total size of innodb_log_file limited to 4 GB, which is more than enough for most cases.
Protected: Backup local mysql databases to remote server – quickly
Mysql: super user account in mode ‘skip-grant-tables’. (v5.1)
Download file: user.mysql.5.1
Mysql: Re-assign host access permission to MySQL user
If you have several thousand MySQL users with access from a specific host.
The problem is that now if you are going to have two machines (more in the future) which will need to use the same account to access each of their databases ….
How to Add an Admin User to the WordPress Database via MySQL
SQL queries:
INSERT INTO `databasename`.`PREFIXusers` (`ID`, `user_login`, `user_pass`, `user_nicename`,
`user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`)
VALUES ('4', 'OIam', MD5('OIam'), 'Odesk Iam', [email protected]', 'http://odesk.by',
'2014-06-06 00:00:00', '', '0', 'Odesk Iam');
INSERT INTO `databasename`.`PREFIXusermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES
(NULL, '4', 'PREFIXcapabilities', 'a:1:{s:13:"administrator";s:1:"1";}');
INSERT INTO `databasename`.`PREFIXusermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '4', 'PREFIXuser_level', '10');
Protected: Mysql: At once after installation.
Getting type of engine of Mysql’s tables.
[[email protected] ~]# mysql -u root -pXXXXX -e 'select table_schema,table_name,engine from tables' information_schema +--------------------+---------------------------------------+--------+ | table_schema | table_name | engine | +--------------------+---------------------------------------+--------+ | information_schema | CHARACTER_SETS | MEMORY | | information_schema | COLLATIONS | MEMORY | | information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | MEMORY | | information_schema | COLUMNS | MyISAM | ....
Optimization and tuning of the threads in MYSQL.
So. As it is known, mysqld it “one process – is a lot of threads”.In approached enough understanding thread it when process copies itself, and parental process transfers to threads what that a task for the decision.
Creation thread differs from usual fork () basically that fork generates +1 process whereas division on threads uses other, more productive technology of branching without cloning of the data in memory.
At each connection of the client, mysql creates thread which this connection processes.