Dumping and importing with backslash












1















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!










share|improve this question














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.




















    1















    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!










    share|improve this question














    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.


















      1












      1








      1








      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!










      share|improve this question














      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






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      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.
























          2 Answers
          2






          active

          oldest

          votes


















          0














          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} |
          +------+-------------+





          share|improve this answer
























          • 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



















          0














          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.






          share|improve this answer
























          • 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











          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
          });


          }
          });














          draft saved

          draft discarded


















          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









          0














          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} |
          +------+-------------+





          share|improve this answer
























          • 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
















          0














          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} |
          +------+-------------+





          share|improve this answer
























          • 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














          0












          0








          0







          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} |
          +------+-------------+





          share|improve this answer













          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} |
          +------+-------------+






          share|improve this answer












          share|improve this answer



          share|improve this answer










          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



















          • 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













          0














          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.






          share|improve this answer
























          • 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
















          0














          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.






          share|improve this answer
























          • 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














          0












          0








          0







          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.






          share|improve this answer













          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.







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Jan 13 '17 at 16:14









          Rick JamesRick James

          41.7k22258




          41.7k22258













          • 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



















          • 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

















          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


















          draft saved

          draft discarded




















































          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.




          draft saved


          draft discarded














          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





















































          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







          Popular posts from this blog

          Liste der Baudenkmale in Friedland (Mecklenburg)

          Single-Malt-Whisky

          Czorneboh