Dumping and importing with backslash
Background
I have a MySQL database, from which I run mysqldump for backups.
In this database, I have some fields that will contain some backslashes, and when dumping this, it will contain something like
INSERT INTO table1 (col1, col2) VALUES (1, 'frac{1}{2}');
(For reference, this is TeX, used in this case for typesetting mathematics.)
Problem
Now, when I import this backup with
mysql -u username -p database_name < file.sql
the backslash in the col2 value disappears. Likely, instead of inserting it, it's being used to escape the following character.
Question
Is there a way to insert this while maintaining the backslash?
My idea so far is to make a script that plows through the file and replaces every occurrence of with \. Is this reliable?
I would assume that someone already thought of this problem and maybe created some way of dealing with it.
Any help appreciated!
mysql backup import
asked Jan 13 '17 at 9:18
AlecAlec
1113
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
Background
I have a MySQL database, from which I run mysqldump for backups.
In this database, I have some fields that will contain some backslashes, and when dumping this, it will contain something like
INSERT INTO table1 (col1, col2) VALUES (1, 'frac{1}{2}');
(For reference, this is TeX, used in this case for typesetting mathematics.)
Problem
Now, when I import this backup with
mysql -u username -p database_name < file.sql
the backslash in the col2 value disappears. Likely, instead of inserting it, it's being used to escape the following character.
Question
Is there a way to insert this while maintaining the backslash?
My idea so far is to make a script that plows through the file and replaces every occurrence of with \. Is this reliable?
I would assume that someone already thought of this problem and maybe created some way of dealing with it.
Any help appreciated!
mysql backup import
asked Jan 13 '17 at 9:18
AlecAlec
1113
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
Background
I have a MySQL database, from which I run mysqldump for backups.
In this database, I have some fields that will contain some backslashes, and when dumping this, it will contain something like
INSERT INTO table1 (col1, col2) VALUES (1, 'frac{1}{2}');
(For reference, this is TeX, used in this case for typesetting mathematics.)
Problem
Now, when I import this backup with
mysql -u username -p database_name < file.sql
the backslash in the col2 value disappears. Likely, instead of inserting it, it's being used to escape the following character.
Question
Is there a way to insert this while maintaining the backslash?
My idea so far is to make a script that plows through the file and replaces every occurrence of with \. Is this reliable?
I would assume that someone already thought of this problem and maybe created some way of dealing with it.
Any help appreciated!
mysql backup import
asked Jan 13 '17 at 9:18
AlecAlec
1113
Background
I have a MySQL database, from which I run mysqldump for backups.
In this database, I have some fields that will contain some backslashes, and when dumping this, it will contain something like
INSERT INTO table1 (col1, col2) VALUES (1, 'frac{1}{2}');
(For reference, this is TeX, used in this case for typesetting mathematics.)
Problem
Now, when I import this backup with
mysql -u username -p database_name < file.sql
the backslash in the col2 value disappears. Likely, instead of inserting it, it's being used to escape the following character.
Question
Is there a way to insert this while maintaining the backslash?
My idea so far is to make a script that plows through the file and replaces every occurrence of with \. Is this reliable?
I would assume that someone already thought of this problem and maybe created some way of dealing with it.
Any help appreciated!
mysql backup import
mysql backup import
asked Jan 13 '17 at 9:18
AlecAlec
1113
asked Jan 13 '17 at 9:18
AlecAlec
1113
asked Jan 13 '17 at 9:18
AlecAlec
1113
asked Jan 13 '17 at 9:18
AlecAlec
1113
asked Jan 13 '17 at 9:18
AlecAlec
1113
1113
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
If you use mysqldump to export your data it will take care of this for you. For example:
mysql> select * from table1;
+------+-------------+
| id | mystr |
+------+-------------+
| 1 | frac{1}{2} |
+------+-------------+
mysqldump -uroot -p database1 table1 > table1.sql
cat table1.sql | grep VALUES
INSERT INTO `table1` VALUES (1,'\frac{1}{2}');
notice the escaped backslash? and:
mysql -uroot -p database1 < table1.sql
mysql -uroot -p database1 -e "select * from table1"
Enter password:
+------+-------------+
| id | mystr |
+------+-------------+
| 1 | frac{1}{2} |
+------+-------------+
answered Jan 13 '17 at 10:59
silokosiloko
1311
Oh, that's odd. My dumps aren't getting the doubled backslash. Is there some setting for that?
– Alec
Jan 13 '17 at 20:26
What you see is what you get. Default mysqldump for MySQL version 5.7.
– siloko
Jan 13 '17 at 20:28
add a comment |
In what context did you run the INSERT? Most client languages have a way of "escaping what needs to be escaped for MySQL". Such a function would take care of doubling up the backslash and dealing with quotes.
answered Jan 13 '17 at 16:14
Rick JamesRick James
41.7k22258
TheINSERTwas run by connecting to the database and running commands from the dumped file.
– Alec
Jan 13 '17 at 20:26
Are you saying that the output from mysql dump had a single backslash in that context?
– Rick James
Jan 13 '17 at 20:31
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f160970%2fdumping-and-importing-with-backslash%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you use mysqldump to export your data it will take care of this for you. For example:
mysql> select * from table1;
+------+-------------+
| id | mystr |
+------+-------------+
| 1 | frac{1}{2} |
+------+-------------+
mysqldump -uroot -p database1 table1 > table1.sql
cat table1.sql | grep VALUES
INSERT INTO `table1` VALUES (1,'\frac{1}{2}');
notice the escaped backslash? and:
mysql -uroot -p database1 < table1.sql
mysql -uroot -p database1 -e "select * from table1"
Enter password:
+------+-------------+
| id | mystr |
+------+-------------+
| 1 | frac{1}{2} |
+------+-------------+
answered Jan 13 '17 at 10:59
silokosiloko
1311
Oh, that's odd. My dumps aren't getting the doubled backslash. Is there some setting for that?
– Alec
Jan 13 '17 at 20:26
What you see is what you get. Default mysqldump for MySQL version 5.7.
– siloko
Jan 13 '17 at 20:28
add a comment |
If you use mysqldump to export your data it will take care of this for you. For example:
mysql> select * from table1;
+------+-------------+
| id | mystr |
+------+-------------+
| 1 | frac{1}{2} |
+------+-------------+
mysqldump -uroot -p database1 table1 > table1.sql
cat table1.sql | grep VALUES
INSERT INTO `table1` VALUES (1,'\frac{1}{2}');
notice the escaped backslash? and:
mysql -uroot -p database1 < table1.sql
mysql -uroot -p database1 -e "select * from table1"
Enter password:
+------+-------------+
| id | mystr |
+------+-------------+
| 1 | frac{1}{2} |
+------+-------------+
answered Jan 13 '17 at 10:59
silokosiloko
1311
Oh, that's odd. My dumps aren't getting the doubled backslash. Is there some setting for that?
– Alec
Jan 13 '17 at 20:26
What you see is what you get. Default mysqldump for MySQL version 5.7.
– siloko
Jan 13 '17 at 20:28
add a comment |
If you use mysqldump to export your data it will take care of this for you. For example:
mysql> select * from table1;
+------+-------------+
| id | mystr |
+------+-------------+
| 1 | frac{1}{2} |
+------+-------------+
mysqldump -uroot -p database1 table1 > table1.sql
cat table1.sql | grep VALUES
INSERT INTO `table1` VALUES (1,'\frac{1}{2}');
notice the escaped backslash? and:
mysql -uroot -p database1 < table1.sql
mysql -uroot -p database1 -e "select * from table1"
Enter password:
+------+-------------+
| id | mystr |
+------+-------------+
| 1 | frac{1}{2} |
+------+-------------+
answered Jan 13 '17 at 10:59
silokosiloko
1311
If you use mysqldump to export your data it will take care of this for you. For example:
mysql> select * from table1;
+------+-------------+
| id | mystr |
+------+-------------+
| 1 | frac{1}{2} |
+------+-------------+
mysqldump -uroot -p database1 table1 > table1.sql
cat table1.sql | grep VALUES
INSERT INTO `table1` VALUES (1,'\frac{1}{2}');
notice the escaped backslash? and:
mysql -uroot -p database1 < table1.sql
mysql -uroot -p database1 -e "select * from table1"
Enter password:
+------+-------------+
| id | mystr |
+------+-------------+
| 1 | frac{1}{2} |
+------+-------------+
answered Jan 13 '17 at 10:59
silokosiloko
1311
answered Jan 13 '17 at 10:59
silokosiloko
1311
answered Jan 13 '17 at 10:59
silokosiloko
1311
answered Jan 13 '17 at 10:59
silokosiloko
1311
1311
Oh, that's odd. My dumps aren't getting the doubled backslash. Is there some setting for that?
– Alec
Jan 13 '17 at 20:26
What you see is what you get. Default mysqldump for MySQL version 5.7.
– siloko
Jan 13 '17 at 20:28
add a comment |
Oh, that's odd. My dumps aren't getting the doubled backslash. Is there some setting for that?
– Alec
Jan 13 '17 at 20:26
What you see is what you get. Default mysqldump for MySQL version 5.7.
– siloko
Jan 13 '17 at 20:28
Oh, that's odd. My dumps aren't getting the doubled backslash. Is there some setting for that?
– Alec
Jan 13 '17 at 20:26
Oh, that's odd. My dumps aren't getting the doubled backslash. Is there some setting for that?
– Alec
Jan 13 '17 at 20:26
What you see is what you get. Default mysqldump for MySQL version 5.7.
– siloko
Jan 13 '17 at 20:28
What you see is what you get. Default mysqldump for MySQL version 5.7.
– siloko
Jan 13 '17 at 20:28
add a comment |
In what context did you run the INSERT? Most client languages have a way of "escaping what needs to be escaped for MySQL". Such a function would take care of doubling up the backslash and dealing with quotes.
answered Jan 13 '17 at 16:14
Rick JamesRick James
41.7k22258
TheINSERTwas run by connecting to the database and running commands from the dumped file.
– Alec
Jan 13 '17 at 20:26
Are you saying that the output from mysql dump had a single backslash in that context?
– Rick James
Jan 13 '17 at 20:31
add a comment |
In what context did you run the INSERT? Most client languages have a way of "escaping what needs to be escaped for MySQL". Such a function would take care of doubling up the backslash and dealing with quotes.
answered Jan 13 '17 at 16:14
Rick JamesRick James
41.7k22258
TheINSERTwas run by connecting to the database and running commands from the dumped file.
– Alec
Jan 13 '17 at 20:26
Are you saying that the output from mysql dump had a single backslash in that context?
– Rick James
Jan 13 '17 at 20:31
add a comment |
In what context did you run the INSERT? Most client languages have a way of "escaping what needs to be escaped for MySQL". Such a function would take care of doubling up the backslash and dealing with quotes.
answered Jan 13 '17 at 16:14
Rick JamesRick James
41.7k22258
In what context did you run the INSERT? Most client languages have a way of "escaping what needs to be escaped for MySQL". Such a function would take care of doubling up the backslash and dealing with quotes.
answered Jan 13 '17 at 16:14
Rick JamesRick James
41.7k22258
answered Jan 13 '17 at 16:14
Rick JamesRick James
41.7k22258
answered Jan 13 '17 at 16:14
Rick JamesRick James
41.7k22258
answered Jan 13 '17 at 16:14
Rick JamesRick James
41.7k22258
41.7k22258
TheINSERTwas run by connecting to the database and running commands from the dumped file.
– Alec
Jan 13 '17 at 20:26
Are you saying that the output from mysql dump had a single backslash in that context?
– Rick James
Jan 13 '17 at 20:31
add a comment |
TheINSERTwas run by connecting to the database and running commands from the dumped file.
– Alec
Jan 13 '17 at 20:26
Are you saying that the output from mysql dump had a single backslash in that context?
– Rick James
Jan 13 '17 at 20:31
The
INSERT was run by connecting to the database and running commands from the dumped file.– Alec
Jan 13 '17 at 20:26
The
INSERT was run by connecting to the database and running commands from the dumped file.– Alec
Jan 13 '17 at 20:26
Are you saying that the output from mysql dump had a single backslash in that context?
– Rick James
Jan 13 '17 at 20:31
Are you saying that the output from mysql dump had a single backslash in that context?
– Rick James
Jan 13 '17 at 20:31
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f160970%2fdumping-and-importing-with-backslash%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
