mysqldump -u username -ppassword –all-databases > dump.sql

text

1

How to Dump All Databases in MySQL Server

mysqldump -u username -ppassword –all-databases > dump.sql

copy | embed

• backup, 
• mysql

mysql -p -h DBSERVER dbname < dbname.sql

sql

1

mysql file into db

mysql -p -h DBSERVER dbname < dbname.sql

copy | embed

• dump, 
• import, 
• mysql

mysqldump -u DBUSER -p DBNAME > DBNAME.sql

sql

1

mysql dump into file

mysqldump -u DBUSER -p DBNAME > DBNAME.sql

copy | embed

• dump, 
• mysql

CREATE OR REPLACE VIEW #__foo_users AS
SELECT u.*
FROM jos_users AS u;


CREATE TABLE IF NOT EXISTS `#__foo_barusers` (
  `foo_barusers_id` bigint(20) unsigned NOT NULL auto_increment,
  `foo_bar_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_by` int(11) NOT NULL default 0,
  `modified_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified_by` int(11) NOT NULL default 0,
  PRIMARY KEY ( `foo_barusers_id` )
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE OR REPLACE VIEW #__foo_view_barusers AS 
SELECT bu.*, 
	b.name AS bar,
	b.enabled AS bar_enabled, 
	b.access AS bar_access,
	u.name AS name,
	u.username AS username,
	u.email	AS	email,
	u.usertype AS usertype
FROM #__foo_barusers AS bu
LEFT JOIN #__foo_bars AS b ON b.foo_bar_id = bu.foo_bar_id
LEFT JOIN #__foo_users AS u ON u.id = mu.user_id;

php

1

Related users tables and views

CREATE OR REPLACE VIEW #__foo_users AS
SELECT u.*
FROM jos_users AS u;


CREATE TABLE IF NOT EXISTS `#__foo_barusers` (
  `foo_barusers_id` bigint(20) unsigned NOT NULL auto_increment,
  `foo_bar_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `created_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `created_by` int(11) NOT NULL default 0,
  `modified_on` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified_by` int(11) NOT NULL default 0,
  PRIMARY KEY ( `foo_barusers_id` )
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

CREATE OR REPLACE VIEW #__foo_view_barusers AS 
SELECT bu.*, 
	b.name AS bar,
	b.enabled AS bar_enabled, 
	b.access AS bar_access,
	u.name AS name,
	u.username AS username,
	u.email	AS	email,
	u.usertype AS usertype
FROM #__foo_barusers AS bu
LEFT JOIN #__foo_bars AS b ON b.foo_bar_id = bu.foo_bar_id
LEFT JOIN #__foo_users AS u ON u.id = mu.user_id;

copy | embed

• mysql, 
• nooku

load data infile '/var/lib/mysql/your_db/yourcsv.csv' into table magento_orders
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
// if csv exported from linux - use lines terminated by '\n'
// if csv exported from windows use lines terminated by '\r\n'
// if csv exported from mac - use lines terminated by '\r'
(OrderDate,ShippingState,ShippingStateName,ShippingCountry,ShippingCountryName,ItemName,ItemOptions,ItemQtyOrdered)

mysql

1

Import CSV data into mysql table

load data infile '/var/lib/mysql/your_db/yourcsv.csv' into table magento_orders
fields terminated by ','
enclosed by '"'
lines terminated by '\n'
// if csv exported from linux - use lines terminated by '\n'
// if csv exported from windows use lines terminated by '\r\n'
// if csv exported from mac - use lines terminated by '\r'
(OrderDate,ShippingState,ShippingStateName,ShippingCountry,ShippingCountryName,ItemName,ItemOptions,ItemQtyOrdered)

copy | embed

• csv, 
• data, 
• import, 
• mysql

SELECT e.entity_id AS product_id, var.value AS product_name
FROM catalog_product_entity e, eav_attribute eav, catalog_product_entity_varchar var
WHERE 
   e.entity_type_id = eav.entity_type_id 
   AND eav.attribute_code = 'name' 
   AND eav.attribute_id = var.attribute_id
   AND var.entity_id = e.entity_id

text

1

Get Basic Product Info From Magento DB

SELECT e.entity_id AS product_id, var.value AS product_name
FROM catalog_product_entity e, eav_attribute eav, catalog_product_entity_varchar var
WHERE 
   e.entity_type_id = eav.entity_type_id 
   AND eav.attribute_code = 'name' 
   AND eav.attribute_id = var.attribute_id
   AND var.entity_id = e.entity_id

copy | embed

• magento, 
• mysql, 
• sql

UPDATE tblTransaction AS t LEFT JOIN tblEmployee as e
ON e.emp_id = t.emp_id SET t.emp_block = e.emp_block 

mysql

1

MySQL update set

UPDATE tblTransaction AS t LEFT JOIN tblEmployee as e
ON e.emp_id = t.emp_id SET t.emp_block = e.emp_block 

copy | embed

• mysql, 
• queries

Si quieres poder conectarte a MySQL desde una aplicación externa (como Navicat) en otra maquina hay que habilitar conexiones remotas.

# 1. Hay que editar el archivo /etc/mysql/my.cnf

sudo gedit /etc/mysql/my.cnf

# 2. Ya en gedit buscamos con ctrl + F "bind-address" sin comillas
# Comentamos la linea

 bind-address = 127.0.0.1

# dejandola así:

# bind-address = 127.0.0.1

3. Guardamos el archivo y reiniciamos MySQL

sudo restart mysql

----

Listo, con eso podras conectarte a MySQL desde otra aplicación que no esté en el mismo servidor localmente.

bash

1

mysql - conexiones remotas en ubuntu

Si quieres poder conectarte a MySQL desde una aplicación externa (como Navicat) en otra maquina hay que habilitar conexiones remotas.

# 1. Hay que editar el archivo /etc/mysql/my.cnf

sudo gedit /etc/mysql/my.cnf

# 2. Ya en gedit buscamos con ctrl + F "bind-address" sin comillas
# Comentamos la linea

 bind-address = 127.0.0.1

# dejandola así:

# bind-address = 127.0.0.1

3. Guardamos el archivo y reiniciamos MySQL

sudo restart mysql

----

Listo, con eso podras conectarte a MySQL desde otra aplicación que no esté en el mismo servidor localmente.

copy | embed

• mysql, 
• ubuntu

mysqldump -u root -p --database db_name > ~/db_name.sql

mysql

1

mysql backup

mysqldump -u root -p --database db_name > ~/db_name.sql

copy | embed

• mysql

mysql -u root
create database new_database; 
^D

mysqldump -u root old_database | mysql -u root -D new_database

bash

1

copy a mysql database

mysql -u root
create database new_database; 
^D

mysqldump -u root old_database | mysql -u root -D new_database

copy | embed

• database, 
• mysql

mysql -u user -ppassword -e "SELECT CONCAT('UPDATE ',table_schema,'.',table_name, ' SET ',column_name, '=REPLACE(',column_name,',''old_string'',''new_string'');') FROM information_schema.columns WHERE table_schema = 'database_name'" | sed '1d' | mysql -u user -ppassword database_name

sql

1

MySQL global search & replace

mysql -u user -ppassword -e "SELECT CONCAT('UPDATE ',table_schema,'.',table_name, ' SET ',column_name, '=REPLACE(',column_name,',''old_string'',''new_string'');') FROM information_schema.columns WHERE table_schema = 'database_name'" | sed '1d' | mysql -u user -ppassword database_name

copy | embed

• bash, 
• mysql, 
• replace, 
• search

SET @orderId = '200000001';

SET FOREIGN_KEY_CHECKS = 1;

DELETE FROM sales_flat_order
    WHERE increment_id = @orderId;
    
DELETE FROM sales_flat_order_grid
    WHERE increment_id = @orderId;

DELETE FROM sales_flat_quote
    WHERE reserved_order_id = @orderId;

DELETE FROM sales_flat_shipment
    WHERE increment_id = @orderId;

DELETE FROM sales_flat_shipment_grid
    WHERE order_increment_id = @orderId;

DELETE from sales_flat_invoice
    WHERE increment_id = @orderId;

DELETE from sales_flat_invoice_grid
    WHERE order_increment_id = @orderId; 

mysql

1

delete magetno order

SET @orderId = '200000001';

SET FOREIGN_KEY_CHECKS = 1;

DELETE FROM sales_flat_order
    WHERE increment_id = @orderId;
    
DELETE FROM sales_flat_order_grid
    WHERE increment_id = @orderId;

DELETE FROM sales_flat_quote
    WHERE reserved_order_id = @orderId;

DELETE FROM sales_flat_shipment
    WHERE increment_id = @orderId;

DELETE FROM sales_flat_shipment_grid
    WHERE order_increment_id = @orderId;

DELETE from sales_flat_invoice
    WHERE increment_id = @orderId;

DELETE from sales_flat_invoice_grid
    WHERE order_increment_id = @orderId; 

copy | embed

• magetno, 
• mysql

CREATE USER drupaluser;
SET PASSWORD FOR drupaluser = PASSWORD(“password”);
GRANT ALL PRIVILEGES ON drupal.* TO drupaluser@localhost IDENTIFIED BY ‘password’;

mysql

1

create mysql user and grant privileges

CREATE USER drupaluser;
SET PASSWORD FOR drupaluser = PASSWORD(“password”);
GRANT ALL PRIVILEGES ON drupal.* TO drupaluser@localhost IDENTIFIED BY ‘password’;

copy | embed

• mysql

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

text

1

wordpress plugin reset

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

copy | embed

• mysql, 
• wordpress

<?php

// SQL call where custom product attrib 'promo' == 1

$pcollection = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToSelect('*')
            //->addAttributeToFilter('sku',array('like' => '%ABC%'))
            ->addAttributeToFilter('promo', '1');
foreach($pcollection as $pp)
{
    // method 1
    $_promoProduct = $pp->_data;

    Zend_Debug::dump($_promoProduct); 
    die();
 
    // method 2
    $url = $pp->getProductUrl();
    $name = $this->htmlEscape($pp->getName());
    $img_src = $pp->getThumbnailUrl(325, 215);
    $price = number_format($pp->getPrice(), 2);
    // etc
}

php

1

Magento: SQL filter by custom product attrib

<?php

// SQL call where custom product attrib 'promo' == 1

$pcollection = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToSelect('*')
            //->addAttributeToFilter('sku',array('like' => '%ABC%'))
            ->addAttributeToFilter('promo', '1');
foreach($pcollection as $pp)
{
    // method 1
    $_promoProduct = $pp->_data;

    Zend_Debug::dump($_promoProduct); 
    die();
 
    // method 2
    $url = $pp->getProductUrl();
    $name = $this->htmlEscape($pp->getName());
    $img_src = $pp->getThumbnailUrl(325, 215);
    $price = number_format($pp->getPrice(), 2);
    // etc
}

copy | embed

• magento, 
• mysql, 
• products

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

bash

1

drop all tables

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

copy | embed

• mysql

<?php
if (!file_exists('wp-config.php')) exit('Could not find wp-config.php, please make sure you place this file in the same directory as all your WP files.');

require 'wp-config.php';

$link = @mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if (!$link) exit('Could not connect to MySQL');
mysql_select_db(DB_NAME, $link) or exit('Could not connect to MySQL');

if (mysql_query('UPDATE `' . $table_prefix . "options` SET `option_value` = 'NEW SITE URL HERE!!!' WHERE `option_name` = 'siteurl'", $link) . "' LIMIT 1", $link)) echo 'Step 1 - done';
else echo 'Could not update: ' . mysql_error($link);
if (mysql_query('UPDATE `' . $table_prefix . "options` SET `option_value` = 'NEW SITE URL HERE!!!' WHERE `option_name` = 'home'", $link) . "' LIMIT 1", $link)) echo 'Step 12- done';
else echo 'Could not update: ' . mysql_error($link);

mysql_close($link);
?>

php

1

Change Wordpress Site URL and URL Home

<?php
if (!file_exists('wp-config.php')) exit('Could not find wp-config.php, please make sure you place this file in the same directory as all your WP files.');

require 'wp-config.php';

$link = @mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
if (!$link) exit('Could not connect to MySQL');
mysql_select_db(DB_NAME, $link) or exit('Could not connect to MySQL');

if (mysql_query('UPDATE `' . $table_prefix . "options` SET `option_value` = 'NEW SITE URL HERE!!!' WHERE `option_name` = 'siteurl'", $link) . "' LIMIT 1", $link)) echo 'Step 1 - done';
else echo 'Could not update: ' . mysql_error($link);
if (mysql_query('UPDATE `' . $table_prefix . "options` SET `option_value` = 'NEW SITE URL HERE!!!' WHERE `option_name` = 'home'", $link) . "' LIMIT 1", $link)) echo 'Step 12- done';
else echo 'Could not update: ' . mysql_error($link);

mysql_close($link);
?>

copy | embed

• mysql, 
• phpmyadmin, 
• script, 
• wordpress

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

bash

1

Optimize all tables on MySQL database

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

copy | embed

• database, 
• mysql, 
• optimize, 
• repair, 
• tables

SELECT * FROM tblModeratedComments WHERE dateCreated >= '2010-04-01' AND dateCreated < '2010-05-01'

sql

1

Search for records between two dates

SELECT * FROM tblModeratedComments WHERE dateCreated >= '2010-04-01' AND dateCreated < '2010-05-01'

copy | embed

• mysql, 
• sql

create database keystone default character set = 'utf8' \
   default collate = 'utf8_general_ci';

bash

1

Create new databases with UTF8 explicitly specified

create database keystone default character set = 'utf8' \
   default collate = 'utf8_general_ci';

copy | embed

• mysql, 
• utf-8