Source:

http://mysql.wingtiplabs.com/documentation/row639ae/configure-row-based-or-mixed-mode-replication

http://www.xaprb.com/blog/2012/08/23/avoiding-statement-based-replication-warnings/

By default, MySQL binary logging and replication is statement-based: when the master server commits a change, it writes the SQL statement into its binary log, and any slaves that replicate it execute the same SQL statement into their own database.

MySQL also supports row-based replication: the master server logs the data affected by a change (information to INSERT or UPDATE, the identity of rows to DELETE), and the slave applies those changes directly to its database.

Row-based replication was introduced to provide perfect replication of data that is non-deterministic: when the same statement was executed on the master and slave, the outcome was different.

It can also have a performance impact. Short SQL queries that affect a lot of rows would require more bandwidth to transmit as row-based replication. For example, if replicated by row, this statement would have to uniquely identify 10,000 rows; it would be much more efficiently transmitted as statement-based:

DELETE FROM important.stuff WHERE id BETWEEN 1 AND 10000;

But difficult to evaluate queries that change relatively little data would be much faster to apply to slaves if the master told them what to change instead of forcing them to repeat the work. For example, this statement requires a table scan and a hash calculation on every row; slaves could save considerable processor time if the master just told them the outcome of all those calculations using row-based replication:

DELETE FROM user WHERE MD5(User) = "c498faa0787b2eaf054b81f814b1aa12";

The MySQL documentation recommends you use Mixed Mode replication. In Mixed Mode replication, most queries are replicated by statement. But transactions MySQL knows are non-deterministic are replicated by row.

Mixed Mode uses row-based replication for any transaction that:

  • Uses user defined functions
  • Uses the
    UUID()

    ,

    USER()

    , or

    CURRENT_USER()

    functions

  • Uses
    LOAD_FILE

    (which otherwise assumes every slave has the exact same file on the local file system and doesn’t replicate the data)

  • Updates two tables with
    auto_increment

    columns (the binlog format only carries one

    auto_increment

    value per statement)

Migrating MySQL database from one Windows Plesk Server to other Windows Plesk Server

The only condition is that the database Engine should be an INNODB. Create the database , database user and password on the plesk server to which you want to migrate the database. Assume that the database name is demodb . Now login to the server from which you want to move the database . Go to the path of the backup E:Program Files (x86)ParallelsPleskDatabasesMySQLdata. Copy the folder “demodb” and paste it on the other server where you want to move at the path C:Program Files (x86)ParallelsPleskDatabasesMySQLdata.

Login to plesk as admin, go to Database Servers, Local Mysql Server, click on the database name and delete the database user and recreate it .

The Path of MySQL Databases on windows plesk server is C:Program Files (x86)ParallelsPleskDatabasesMySQLdata

When you are using MySQL, you will (likely) have tables that can be fragmented. In MySQL terms this is called “OPTIMIZE”.

You could simply OPTIMIZE every table in every database, but during an OPTIMIZE, the tables are locked, so writing is not possible.

To minimize the time that MySQL will be locked (and results cannot be written), here is a script that checks fragmentation of every table of every database. Only if a table is fragmented, the table is OPTIMIZED.

#!/bin/sh

echo -n "MySQL username: " ; read username
echo -n "MySQL password: " ; stty -echo ; read password ; stty echo ; echo

mysql -u $username -p"$password" -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do
mysql -u $username -p"$password" -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do
  if [ "$datafree" -gt 0 ] ; then
   fragmentation=$(($datafree * 100 / $datalength))
   echo "$database.$name is $fragmentation% fragmented."
   mysql -u "$username" -p"$password" -NBe "OPTIMIZE TABLE $name;" "$database"
  fi
done
done

Another one:

#!/bin/sh

# --- Variabls
MYSQLROOTUSR='root'
MYSQLROOTPW='password'
PERCENT='55'
ROWCOUNT='300'

mysql -u $MYSQLROOTUSR -p"$MYSQLROOTPW" -NBe "SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, DATA_FREE, (DATA_FREE*100/DATA_LENGTH) as PRC FROM INFORMATION_SCHEMA.TABLES WHERE (DATA_FREE*100/DATA_LENGTH) >= $PERCENT AND TABLE_ROWS >= $ROWCOUNT ORDER BY TABLE_SCHEMA, PRC DESC;" | while read TABLE_SCHEMA TABLE_NAME TABLE_ROWS DATA_LENGTH INDEX_LENGTH DATA_FREE PRC; do

        echo "$TABLE_SCHEMA.$TABLE_NAME is $PRC% fragmented and has $TABLE_ROWS rows."
        mysql -u "$MYSQLROOTUSR" -p"$MYSQLROOTPW" -NBe "OPTIMIZE TABLE $TABLE_NAME;" "$TABLE_SCHEMA"
done

Another tool of a similar nature is the MySQLReport tool which can be found at http://hackmysql.com.

Information can be found here about how to read and analyse the report that is produces from this link http://hackmysql.com/mysqlreportguide.

You can load it up remotely and build it on your server in a similar way making use of an HTTP tool like CURL or WGET etc:

wget hackmysql.com/scripts/mysqlreport

–2011-11-13 02:58:47– http://hackmysql.com/scripts/mysqlreport
Resolving hackmysql.com… 64.13.232.157
Connecting to hackmysql.com|64.13.232.157|:80… connected.
HTTP request sent, awaiting response… 200 OK
Length: 38873 (38K) [application/x-perl]
Saving to: `mysqlreport’

100%[======================================>] 38,873 –.-K/s in 0.1s

2011-11-13 02:58:47 (254 KB/s) – `mysqlreport’ saved [38873/38873]

Once loaded give the newly installed file execute permission with the following command

chmod +x mysqlreport1.pl

You then call it by passing through the details of the system you want to analyse e.g:


mysqlreport --user root --host localhost --password mypsw100

Download/Installation
Simple

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
# chmod +x mysqltuner.pl
# ./mysqltuner.pl

You can download the entire repository by using ‘git clone’ followed by the cloning URL above. The simplest and shortest method is:

wget mysqltuner.pl
perl mysqltuner.pl

Change the permissions you can execute it without calling perl directly.

[root@austin ~]# chmod +x mysqltuner.pl

[root@austin ~]# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.73-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4M (Tables: 230)
[--] Data in InnoDB tables: 12M (Tables: 419)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 423

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 16s (25 q [1.562 qps], 8 conn, TX: 62K, RX: 1K)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 58.0M global + 1.6M per thread (151 max threads)
[OK] Maximum possible memory usage: 303.4M (3% of installed RAM)
[OK] Slow queries: 0% (0/25)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/1.1M
[!!] Key buffer hit rate: 77.6% (98 cached / 22 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts)
[OK] Temporary tables created on disk: 20% (1 on disk / 5 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 68% (15 open / 22 opened)
[OK] Open file limit used: 3% (32/1K)
[OK] Table locks acquired immediately: 100% (29 immediate / 29 locks)
[!!] Connections aborted: 25%
[OK] InnoDB data size / buffer pool: 12.1M/16.0M

[stextbox id=”warning”]——– Recommendations —————————————————–[/stextbox]

General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours – recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)

 

Optimize tables

See – http://geekdecoder.com/mysql-checkrepair-database/

Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
query_cache_size (>= 8M)
thread_cache_size (start at 4)

 

[root@austin ~]# nano /etc/my.cnf

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
interactive_timeout=1800
wait_timeout=1800

Add the following if mysql is 5.6.1 or higher

slow_query_log = 1;
slow_query_log_file = '/var/log/mysql-slow.log';
query_cache_type = 1
query_cache_size = 32M
thread_cache_size = 4

Other links and Info:

I would recommend making sure the following is optimized.

Your mysql queries in your code to make sure the queries are being closed.
Your mysql tables are optimized.
Your mysql configuration is optimized for your web site needs.

Here are some helpful links on mysql optimization.

http://www.codero.com/knowledge-base/questions/319/How+to+install+mytop+for+database+performance+monitoring%3A
http://www.codero.com/knowledge-base/questions/298/How+can+I+view+currently+running+MySQL+queries%3F
http://www.codero.com/knowledge-base/questions/274/How+to+check+and+repair+MySQL+database+tables
http://www.codero.com/knowledge-base/questions/96/How+do+I+optimze+mysql%3F
http://www.codero.com/knowledge-base/questions/195/How+can+I+trouble+shoot+my+MySQL+database%3F
http://dev.mysql.com/doc/refman/5.0/en/optimization.html

Check a Specific Table in a Database

If your application gives an error message saying that a specific table is corrupted, execute the mysqlcheck command to check that one table.

The following are some of the key options that you can use along with mysqlcheck.

  • -A, –all-databases Consider all the databases
  • -a, –analyze Analyze tables
  • -1, –all-in-1 Use one query per database with tables listed in a comma separated way
  • –auto-repair Repair the table automatically it if is corrupted
  • -c, –check Check table errors
  • -C, –check-only-changed Check tables that are changed since last check
  • -g, –check-upgrade Check for version dependent changes in the tables
  • -B, –databases Check more than one databases
  • -F, –fast Check tables that are not closed properly
  • –fix-db-names Fix DB names
  • –fix-table-names Fix table names
  • -f, –force Continue even when there is an error
  • -e, –extended Perform extended check on a table. This will take a long time to execute.
  • -m, –medium-check Faster than extended check option, but does most checks
  • -o, –optimize Optimize tables
  • -q, –quick Faster than medium check option
  • -r, –repair Fix the table corruption

-c check
-o optimize
-r reapir
-a analyze

Login to mysql on Plesk

[root@austin ~]# mysql -uadmin -p`cat /etc/psa/.psa.shadow`

Check a single table in a database on linux server without plesk

[root@austin ~]# mysqlcheck -c database_name table -u root -p

[stextbox id=”alert” defcaption=”true” mode=”css”]For Plesk[/stextbox]

For repairing a single table

[root@austin ~]# mysqlcheck -r database_name table_name -uadmin -p`cat /etc/psa/.psa.shadow`

Here is the command to run to repair all tables in a database.

[root@austin ~]# mysqlcheck -r database_name -uadmin -p`cat /etc/psa/.psa.shadow`

Here is the command to run for all databases and tables

[root@austin ~]# mysqlcheck --all-databases -u admin -p`cat /etc/psa/.psa.shadow` --auto-repair

For cPanel

# mysqlcheck --all-databases -r #repair
# mysqlcheck --all-databases -a #analyze
# mysqlcheck --all-databases -o #optimize 

Simple:


# mysql -u username -p databasename
# password: ********
mysql> check table tablename;
mysql> repair table tablename;

cPanel

In cPanel, you can repair a database table by:

cPanel -> Databases -> MySQL Databases

You may need to go to Page 2, so under Current Databases [2] or >> to go to the next page. Under Modify Databases click [Check DB] or [Repair DB].

Plesk

Open the database in phpMyAdmin. To do that, go to your Plesk control panel, click “Websites and Domains,” click “Databases,” and then click the database in question. From there, click “Webadmin” under “Tools.” You will most likely be presented with phpMyAdmin.

Next, click “Databases” in the upper right, click the database name, then scroll all the way down and click “Check All” to check all the tables. From the drop down menu, select “Check Tables” and your tables will immediately be checked. After that, you can decide which tables to select and choose “repair” from the drop down menu

Export a database mysql


# mysqldump -u -p username database_name > dbname.sql

Plesk Server


# mysqldump  -u admin -p`cat /etc/psa/.psa.shadow` wordpress_database > domain_backup_7.16.14.sql

To export a single table from your database you would use the following command:


# mysqldump -p --user=username database_name tableName > tableName.sql

Import a database or table

# mysql -p -u username database_name < file.sql [/bash] For Plesk server [bash] # mysql -u admin -p`cat /etc/psa/.psa.shadow` database < /tmp/database.sql [/bash] To import a single table into an existing database you would use the following command: [bash] #mysql -u username -p -D database_name < tableName.sql [/bash]

First you will want to set up subscriptions for the domains in the plesk panel. Once they are created you can run the commands below:

Here are a few commands that should help.

1.) rsync example. This will move the data from one drive to the other.


rsync -avz /mnt/slave/var/www/vhosts/"domain name"/httpdocs /var/www/vhosts/"domain name"/httpdocs

You will want to change the “domain name” to the one you are working on at the time.

2.) Changing ownership:


chown -R "ftpusername":psacln /var/www/vhosts/"domain name"/httpdocs/*

You will want to change the “ftpusername” to what you set up for each domain when creating the subscription.

If the sites are simple, these are the only 3 steps you should need to take for each domain.

Update:

I would use the rsync command rather than cp. Rsync will keep permissions the same. The command would look something like:


rsync -aPSv /mnt/olddrive/var/www/vhosts/domain.com/httpdocs/somedirectory/ /var/www/vhosts/domain.com/httpdocs/restoredirectory/

You can also preview what will be synced by adding –dry-run to the command. This doesn’t actually copy anything, just shows you what’s going to happen e.g.


rsync -aPSv /mnt/olddrive/var/www/vhosts/domain.com/httpdocs/somedirectory/ /var/www/vhosts/domain.com/httpdocs/restoredirectory/ --dry-run

The databases are in /mnt/olddrive/var/lib/mysql

One thing you might try is to stop mysql, change datadir in /etc/my.cnf to /mnt/olddrive/var/lib/mysql, then restart mysql and dump your databases. Then stop mysql, revert datadir back and restart mysql again.

When MySQL is running using the slaved drive as the datadir, you can use this to log in to MySQL:


mysql -uadmin -p`cat /mnt/olddrive/etc/psa/.psa.shadow

alternately you can set skip-grant-tables in the /etc/my.cnf file until you have things running again.

You can either dump your databases as one file or as separate files for each DB – http://www.commandlinefu.com/commands/view/2916/backup-all-mysql-databases-to-individual-files