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).
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.
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; |
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.
| (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”