Mysql Commands

Add columns on table:

 

ALTER TABLE users

ADD COLUMN `count` SMALLINT(6) NOT NULL AFTER `lastname`,

ADD COLUMN `log` VARCHAR(12) NOT NULL AFTER `count`,

ADD COLUMN `status` INT(10) UNSIGNED NOT NULL AFTER `log`;

 

Inner join and Concat rows

SELECT d.dest_id,d.dest_name,b.destination,GROUP_CONCAT(DISTINCT CONCAT(b.name,':' ,b.durationRate) order by b.name ASC) as suppliers,
CONCAT_WS(',', GROUP_CONCAT(DISTINCT b.durationRate)) as prices
FROM destinations d
LEFT JOIN billing_rates_history b
ON d.dest_id=b.destination AND d.domain=b.name
where (b.name='tti.s.uk' or b.name='dvx.pr.s.uk' or b.name='dvx.std.s.uk') AND d.dest_id='9377'
group by dest_id

 

Creating A Backup
The mysqldump command is used to create textfile “dumps” of databases managed by MySQL. These dumps are just files with all the SQL commands needed to recreate the database from scratch. The process is quick and easy.

If you want to back up a single database, you merely create the dump and send the output into a file, like so:

mysqldump database_name > database_name.sql

mysqldump -u root -pPASSWD DB TABLE  > /home/ocruser/db_backups/radacct201605.sql

gzip radacct201605.sql

Multiple databases can be backed up at the same time:

mysqldump --databases database_one database_two > two_databases.sql

In the code above, database_one is the name of the first database to be backed up, and database_two is the name of the second.

It is also simple to back up all of the databases on a server:

mysqldump --all-databases > all_databases.sql

Database Management Made Easy

Restoring a Backup
Since the dump files are just SQL commands, you can restore the database backup by telling mysql to run the commands in it and put the data into the proper database.

mysql database_name < database_name.sql

In the code above, database_name is the name of the database you want to restore, and database_name.sql is the name of the backup file to be restored..

If you are trying to restore a single database from dump of all the databases, you have to let mysql know like this:

​mysql --one-database database_name < all_databases.sql

Restoring Databases From cPanel Backups
There is no special wisdom to pass on to you regarding restoring databases from cPanel backups. If you do not wish to use cPanel's automatic methods of restoring entire accounts, you can open up the individual backup on the command line, find the mysql dump file you wish to restore, and use the above commands to perform the restore.

As always, if you have any questions about how to use this tutorial on your Liquid Web server, do contact our Heroic Support.

 

OCR commands

select * from dr_rules where gwlist!='#678,76' and  gwlist!='#234,72' and  gwlist!='#4344,78'

update dr_rules set gwlist='7,73' where gwlist='73,7'

 

SMSC commnads

select * from log where submit_time>="2016-12-06 00:00:00" order by submit_time desc limit 20

select * from history where connectionid='87' and updatetime>'2016-09-21 00:00:00' and createtime>'2016-09-21 00:00:00'

select * from sms where connectionid='76' and updatetime>'2016-09-21 00:00:00' and createtime>'2016-09-21 00:00:00'

select * from sms where  updatetime>'2016-09-27 00:00:00' and createtime>'2016-09-27 00:00:00' and queue='out' and (connectionid=87 or connectionid=107)

---