2018-10-22         Otis

How to do incremental backup in MySQL using LSN using ansible without using percona?

I wants to backup mysql DB incrementally using ansible/shell commands. Can anyone give me the steps involve in it. I have a database and table in my MySQL. my.cnf file having the configuration for that. Every one hour I wants to do the backup and upload my DB datas to the s3 bucket. Without using LSN value we can achieve the solution.Flush binary logs [new binary log file created]By using binlog.index file, found previous binary file. Copied thatfile to where I want to upload. That is the dump file and I got datafrom that file got restore.File saved by current timestam...

 mysql                     1 answers                     15 view
 2018-10-22         Atwood

best practice for backup MySQL InnoDB tables?

What is the best practice for backing up MySQL InnoDB tables? (for myisam I use backuppc)How can I do a backup and how can I restore it?Thank you. mysql> mysqldump -u username -ppassword databasename tablename > Path to saveyou can schedule in in windows scheduler or in cron job after creating the batch file or SH file. [XXX]You can use 'mysqldump' program from MySQL to dump individual tables..shell> mysqldump [options] [tbl_name ...]To restore the tables use command: shell>mysql -uusername -ppassword -f -D databasename < /path/to/file/dbbackup.sql [XXX]

 mysql                     2 answers                     16 view
 2018-10-22         Eunice

What when mysqldump becomes utterly slow

Currently my database is almost 20 GB big and still growing.I'm taking a daily backup with mysqldump and it's getting really slow.So slow that in the meanwhile new connections stack up and eventually cause this error:SQLSTATE[HY000] [1040] Too many connections(I could improve the amount of connections that's accepted but that won't do anything because the connections are still just frozen, waiting for the backup to complete, which will lead to timeout)I've been reading up on some options to improve the speed and this is what I've found:option --quick (Will probably help)opt...

 mysql                     1 answers                     15 view
 2018-10-22         Burton

Best practice for website database writes during cold backups?

Lets say you have a large, popular database-driven website. There are people on the site all day and all night. They access pages that both read and write to the database.When implementing a daily database backup plan, it involves doing cold backups, which means that you temporarily shutdown the database or lock tables while the backup is in progress. This ensures that new data is not inserted into the database while the backup is happening.What are some good approaches to coding your site controllers and models so that they don't fail due to table locks or the database bei...

 mysql                     1 answers                     22 view
 2018-10-22         Jonas

Handle mysql restart in SQLAlchemy

My Pylons app uses local MySQL server via SQLAlchemy and python-MySQLdb. When the server is restarted, open pooled connections are apparently closed, but the application doesn't know about this and apparently when it tries to use such connection it receives "MySQL server has gone away":File '/usr/lib/pymodules/python2.6/sqlalchemy/engine/default.py', line 277 in do_execute cursor.execute(statement, parameters)File '/usr/lib/pymodules/python2.6/MySQLdb/cursors.py', line 166 in execute self.errorhandler(self, exc, value)File '/usr/lib/pymodules/python2.6/MySQLdb/connections...

 mysql                     2 answers                     62 view
 2018-10-22         Vera

SQLAlchemy intersect error

user1 = User(username='john')user2 = User(username='jim')user1.intersect(user2).all() throws an error when using MariaDB, but not with SQLite.Here's the error:sqlalchemy.exc.ProgrammingError: (_mysql_exceptions.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERSECT SELECT user.id AS user_id, user.username AS user_username, user.email ' at line 4") [SQL: 'SELECT anon_1.user_id AS anon_1_user_id, anon_1.user_username AS anon_1_user_username, anon_1.user_email AS...

 mysql                     1 answers                     61 view
 2018-10-22         Winifred

How can I get golang mysql driver to timeout pings in 2 seconds?

I am having some trouble figuring out how to make a database connection-attempt timeout properly in Go. I am using some of the examples at this excellent resource as a base. I believe I am setting up everything correctly, but my pings just refuse to time out after 2 seconds. I've extracted the code in question into a sample program as follows. Note that there is no database running at mainimport ( "context" "database/sql" _ "github.com/go-sql-driver/mysql" //MySQL driver "log" "time")func main() { log.Print("Trying to ping database!")...

 mysql                     1 answers                     35 view
 2018-10-22         Wade

Nginx Docker Volumes empty

When using docker-compose, nginx isn't showing files like other images might.For example with Mysql, the below code will save the data created at /var/lib/mysql to the local machine at ./volumes/db/data./volumes/db/data:/var/lib/mysqlAnother example, with Wordpress, the below code will save the data created at /var/www/html/wp-content/uploads to the local machine at ./volumes/uploads/data./volumes/uploads/data:/var/www/html/wp-content/uploadsThis is not working with nginx though so no matter what I change /some/nginx/path to, it never appears at ./volumes/nginx/data./volume...

 mysql                     2 answers                     60 view
 2018-10-22         Richard

PDO insert max id+1 not auto_increment

I don't want to use auto increment of mysql ,But I want to get max id+1 to insert as ID for next data.I tried to do as How to get auto increment id by PDO before executeBut no luck.$sql = "INSERT INTO checklist ((SELECT MAX(checklist_id)+1), checklist_name ) VALUES (NULL, :checklist_name)";$pdo_statement = $pdo_conn->prepare( $sql );$result = $pdo_statement->execute ((array(':maxid'=>NULL,':checklist_name'=>$_POST['checklist_name']));Could you please help me what code it should be? Edit: as one of the commentors have pointed out above; it is not recommended t...

 mysql                     1 answers                     69 view
 2018-10-22         Cora

Resize Amazon RDS storage

We are currently working with a 200 GB database and we are running out of space, so we would like to increment the allocated storage.We are using General Purpose (SSD) and a MySQL 5.5.53 database (without Multi-AZ deployment).If I go to the Amazon RDS menu and change the Allocated storage to a bit more (from 200 to 500) I get the following "warnings":Deplete the initial General Purpose (SSD) I/O credits, leading to longer conversion times: What does this mean?Impact instance performance until operation completes: And this is the most important question for me. Can I resize ...

 mysql                     3 answers                     68 view
 2018-10-22         Ed

Difference between "<>some" and "not in"

I was reading the books fundamentals of database systems and in the topic nested subqueries(set comparison) it was written that some and in are identical whereas <>some and not in are not.According to me <>some means "not at least one" and not in means "not in the set"...so I think they should mean the same. They are not the same! <>SOME means: not = a or not = b or not = c...NOT IN means: not = a and not = b and not = c...Hope this will be more clear for you now.For example:SELECT CustomerIDFROM Sales.CustomerWHERE TerritoryID <> ANY (SELECT T...

 mysql                     2 answers                     25 view
 2018-10-22         Everley

About calculations among a few tables joined with UNION

I have difficult query and now I need to devide the result of this query on data from another table. Used UNION. Is it right? Why I doesn't work?SELECT * from (select IFnull(t.diapason,'total') as diapason, count(distinct user_id) / total_visitors*100 AS 'percent_of_users'FROM (SELECT p.user_id, p.amount as total, CASE when amount<10 then '0-10' when amount>=10 then '10 +' END AS diapasonFROM (SELECT payments.user_id, SUM(amount) AS amount FROM payments INNER JOIN (SELECT DISTINCT user_id, login_time FROM activity where login_time between '2018-04-12' and ...

 mysql                     2 answers                     28 view
 2018-10-22         Alfred

Laravel / MySQL when using AVG (only count first row return on a given id) - how?

My AVG(rank) will return 2 and 34 clicksSum in this case. But how can I get it to only count the first row of ranks? So that it will only take one rank of each keyword_id from each date in the interval.$interval = \DB::table('keywords')->selectRaw('keyword, url, AVG(rank) rankAvg, SUM(clicks) clicksSum ')->join('ranks', 'keywords.id', '=', 'ranks.keyword_id')->whereBetween('ranks.date', array('2018-07-20','2018-07-29'))->orderBy('clicksSum', 'DESC')->groupBy('keywords.id')->paginate(50);Table: Keywordsid keyword1 testTable: Ranksid keyword_id rank url ...

 mysql                     1 answers                     50 view
 2018-10-22         Quentin

SQL Count Values From the Top 6

i trying to create an SQL query to count how many times [Over]='Y' in the Top 6 selection and i receive an error in the merge query.Query to get Top 6: SELECT TOP 6 [Number] ,[Over] ,[Goal] FROM [Test_Hermes].[dbo].[Test] ORDER BY Number descQuery to count:SELECT COUNT([Over]) FROM [dbo].[Test] WHERE [OVER] = 'Y';Merge both queries:SELECT COUNT([Over]) FROM (SELECT TOP 6 [Number] ,[Over] ,[Goal]FROM [Test_Hermes].[dbo].[Test]ORDER BY Number desc) WHERE [OVER] = 'Y';any help will be appreciate!Thanks Show this sample pls. SELECT [Number] ,[Over] ,[Goal] , SUM( I...

 mysql                     2 answers                     47 view
 2018-10-22         Pearl

Questions about mysqldump and Microsoft Azure

I need to dump database which hosts on Microsoft Azure.I tried following: 1. Connect to db with MySQL Workbench. 2. Select Data Export and use it. The warning message says that 'unequal version of mysqldump(8.0.12) and MySQL Server to be dumped(5.7.21) may cause issues' were shown. 3. No upgrades avaliable at MySQL Community Installer. So where's the question:1. How to get mysqldump of required version?2. mysqldump version seems higher than needed but it still doent work. Why?All attemps had following log:18:59:01 Dumping schedule-system (all tables)Running: "C:\Program Fi...

 mysql                     1 answers                     123 view
 2018-10-22         Nelly

MYSQL LEFT JOINing the latest record from another table

I have a training_stats table (current due training) and I also have a completed_training table.What I want to do is query due training with the last completed date from the completed table.I've nearly got what I want, I get the due training, but they are duplicated with each completed record(as there are many completed records to each current due), and I only want single rows and the latest completed date.I've been trying to use MAX, and when I run the MAX query independently, I get the last record. But when the MAX query is in the join, it is returning all completed rows....

 mysql                     2 answers                     125 view

Page 1 of 1445  |  Show More Pages:  Top Prev Next Last