UPWORK/ODESK guru: Tips and Tricks for clients

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

Archive for the ‘Mysql’ Category

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

Create root privilege user on MySQL server.

without comments

MySQL root privileges user is “root”, I always remove “root” userid once I got MySQL installed, mainly for security purpose, secondly I do not want stupid thing happened like someone able to brute force into MySQL database.

Read the rest of this entry »

Written by spectre

February 14th, 2014 at 12:39 am

Posted in Mysql