UPWORK/ODESK guru: Tips and Tricks for clients

skype: odesk.by / upwork.link , email: info@odesk.by/upwork.link

Archive for the ‘Mysql’ Category

Conversion table types in MySQL #2

without comments

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):

Read the rest of this entry »

Written by spectre

December 12th, 2016 at 3:07 pm

Posted in Mysql

Convert MyISAM in InnoDB for WordPress

without comments

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 onwp_posts table were getting blocked because MyISAM do not support row-level locking.

Read the rest of this entry »

Written by spectre

November 12th, 2016 at 3:24 pm

Posted in Mysql

Optimal size of innodb_log_file_size

without comments

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.

Read the rest of this entry »

Written by spectre

January 15th, 2016 at 4:16 pm

Posted in Mysql

Protected: Backup local mysql databases to remote server – quickly

without comments

This content is password protected. To view it please enter your password below:

Written by spectre

March 29th, 2015 at 4:03 pm

Posted in Mysql

Mysql: super user account in mode ‘skip-grant-tables’. (v5.1)

without comments

Written by spectre

February 28th, 2015 at 9:39 am

Posted in Mysql

Mysql: Re-assign host access permission to MySQL user

without comments

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 ….

Read the rest of this entry »

Written by spectre

December 21st, 2014 at 3:03 pm

Posted in Mysql

How to Add an Admin User to the WordPress Database via MySQL

without comments

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', 'info@odesk.by', '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');

Written by spectre

June 6th, 2014 at 9:54 pm

Posted in Mysql

Protected: Mysql: At once after installation.

without comments

This content is password protected. To view it please enter your password below:

Written by spectre

April 26th, 2014 at 12:27 am

Posted in Mysql

Getting type of engine of Mysql’s tables.

without comments

[root@server ~]# 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 |
....

Written by spectre

March 17th, 2014 at 11:26 am

Posted in Mysql

Optimization and tuning of the threads in MYSQL.

without comments

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.

Read the rest of this entry »

Written by spectre

February 15th, 2014 at 11:58 am

Posted in Mysql