verbindet 2 tables mit der Id spalte

SELECT *
FROM contacts
INNER JOIN bounce ON contacts.id = bounce.id
LIMIT 0 , 30

ein hirnfigg

SELECT bounce.first_name, bounce.last_name, bounce.primary_email_address, users.name, users.lastname
FROM bounce
LEFT JOIN (
contacts, users
) ON ( bounce.id = contacts.id
AND assigned_user_id = users.id )
ORDER BY `users`.`lastname` ASC

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop

sudo service mysql stop    on newer systems

# mysqld_safe --skip-grant-tables &

Step # 3: Connect to mysql server using mysql client:

# mysql -u root

mysql>
Step # 4: Setup new MySQL root user password

mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop

normally commands are capital letters in the manuals but it’ doesn’t mater if you type in.

mysql -u root -p

use dbname

select email from table_xx where email like ‘%@google.com’;

SELECT *
FROM `contacts`
WHERE `primary_address_country` LIKE ‘CH’
LIMIT 0 , 30;

UPDATE `contacts`
SET `primary_address_city`=’Zurich’
WHERE `primary_address_city` LIKE ‘Zürich CH-8038′;

UPDATE `contacts`
SET `primary_address_country`=’Zurich’
WHERE `primary_address_country` LIKE ‘Zürich CH-8038’;

How to create a copy of a mysql database using mysqldump, create database and source commands and make your tests on that copy of your original(production)database (of course, in case you have one server to test it on).

1. Back-up the original database that you want to “clone”

In case you you have a mysql password for user root you need to use mysql(dump) -u root -p dbnameand you will be asked for root password by mysql program. If you don’t wish to be asked you can youse mysql(dump) -u root -ppassword (yes, one word) dbname to access or backup the database (mysqldump) and you can redirect the output (in case of backup) to a file (preferably .sql file).

$ mysqldump -u root -p dbname >~/db_name.sql

I used home directory absolute path because we will use it in step 3 – Populating the copy of the mysql database.

2. Creating the copy of the mysql database

Now that we have the file that is the database backup (~/db_name.sql) we can go ahead and connect to mysql database using mysql command:

$ mysql -u root -pmypassword
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 812
Server version: 5.1.20-beta-log FreeBSD port: mysql-server-5.1.20

Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the buffer.

mysql> create database dbname_copy;
mysql> use dbname_copy;

note that mysql> is mysql prompt. From here you can give all commands thatyou will give to this mysql db.
1st command creates a new database and 2nd command tells the client to use that database.

3. Populating the copy of the mysql database

(previous step use dbname_copy)

mysql> source ~/db_name.sql;

or just mysql -u root -pyourpassword < thedbname.sql

This command populates the copy of your database with the contents of your original database backed up in ~/db_name.sql file.

pt-query-digest –limit 100 ~/Desktop/mysql-slow.log.2 ~/Desktop/mysql-slow.log.1 ~/Desktop/mysql-slow.log >> ~/Desktop/slow-log-statistics-3d.txt

You have to find on the internet, download and install the program first

you can directly mail like:

pt-query-digest –limit 50 /var/log/mysql/mysql-slow.log | mailx -s “performance charts from the X Server” “programer@test.com”